How do I assign a numeric value to a text letter

I have a rota worksheet that contains columns for start & finish times each 
weekday and a final column that adds the total hours worked. I would like to 
be able to enter text such as Holiday or Training in the daily columns but it 
causes an error msg in the total hours cell.

I would like to asssign a value to the text, eg HOL would be considered as a 
normal day - 8 hrs - and included in the total hrs.
0
Shaun (103)
9/17/2005 4:56:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
568 Views

Similar Articles

[PageSpeed] 28

I've solved a similar problem by having 2 columns for each daily column. The 
first column is the "type" of work eg: NORM or HOL etc. The second column 
records the relevant "hours" eg: 8, 4, 0 etc. The total column only add the 
hours columns. You can create a lookup table so that when you put HOL in the 
"type" column then 8 (for example) is shown in the hours column. 

HTH
-- 
Peter
London, UK


"Shaun" wrote:

> I have a rota worksheet that contains columns for start & finish times each 
> weekday and a final column that adds the total hours worked. I would like to 
> be able to enter text such as Holiday or Training in the daily columns but it 
> causes an error msg in the total hours cell.
> 
> I would like to asssign a value to the text, eg HOL would be considered as a 
> normal day - 8 hrs - and included in the total hrs.
0
Peter2031 (452)
9/17/2005 5:33:01 PM
You don't mention excluding unpaid meal breaks so with times entered as XL 
Times try:

=SUM(B2,D2,F2,H2,J2)-SUM(A2,C2,E2,G2,I2)+COUNTIF(A2:J2,"*")/6

The SUM will ignore text and, provided the text is in pairs, it will add 8 
hours.
(One BIG failing is that with incorrect dtat a figure will still be returned 
and errors willl not be highlighted by #ERROR!)

I therefore don't say that it is a good solution.  I can't help but feel 
that you would be better re-desigining your spreadsheet.

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

"Shaun" <Shaun@discussions.microsoft.com> wrote in message 
news:83E7EFAD-DD21-4F7B-B977-F847620AAE83@microsoft.com...
>I have a rota worksheet that contains columns for start & finish times each
> weekday and a final column that adds the total hours worked. I would like 
> to
> be able to enter text such as Holiday or Training in the daily columns but 
> it
> causes an error msg in the total hours cell.
>
> I would like to asssign a value to the text, eg HOL would be considered as 
> a
> normal day - 8 hrs - and included in the total hrs. 


0
sandymann2 (1054)
9/17/2005 11:24:23 PM
Reply:

Similar Artilces:

Limiting Security Role Assignment
My question regards the ability of an administrator to assign the System Administrator security role to any user. We want to give select users the ability to manage the business users for our CRM instance. This includes creating user records, and assigning various security roles to them. However, we don't want the person with this role to have the ability to customise CRM forms and suchlike. It seems though that we cannot prevent anyone with the the Assign Role privilege from being able to assign any role (including System Administrator) to anyone they wish, including themselves. I...

addition with a maximum value #4
djarcadian Wrote: > Nevermind. Figured it out! > > =IF(SUM(D10+E10)>20,20,(SUM(D10+E10)+F10)) Actually, no... that doesn't work. When D10 and E10 equal less than 2 and F10 brings it over 20 it's fine but when D10 and E10 equal mor than 20 then the results default back to 20. Hmmmmm. What do I do -- djarcadia ----------------------------------------------------------------------- djarcadian's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1587 View this thread: http://www.excelforum.com/showthread.php?threadid=31496 Try: =IF(F10>0,SUM...

compare values in excel list and mark differences?
I need to compare sets of values and generate a list of the differences. I have a list of countries that I need feedback from and I have a separate list that I have received feedback from. Does anyone know how to generate a list of the differences? ...

URGENT : How to get CHECKBOX object value from a Excel Worksheet by a C# Class
As subject... I wanna get the value of some Visual Basic Object (Checkbox) in a Excel Worksheet by calling a C# class / VB.NET class.... May I ask for advice ??? What I got ....only Excel application, workbook, worksheet....and so on... Excel.Application excelApp = new Excel.ApplicationClass(); Excel.Workbook excelWorkbook; Excel.Sheets excelSheets; Excel.Worksheet excelWorksheet ; I am not familiar with c#, however the checkbox objects parent is the worksheet. For example in VBA Worksheets("sheet2").CheckBox1.Value - contains the "checked" indicator You i...

Changing signatures in Outlook 2003 with plain text?
I use plain text, non-html for my message formatting in Outlook 2003. To kludge using form letters, I have several "signatures" set up that are actually full-blown messages. All I need to do is pick the appropriate one, put a name after "Dear " and send the message. A colleague has his default email set up for html messages. If he starts a new message, he just right-clicks in the message window and a context menu comes up with a choice of "change signature." I don't get that option though with plain text. What am I doing wrong? I can do a couple wo...

Sort Treats Empty Cells As Largest Value??
I have several projects that sort data from greatest to least (descending). That is, nothing is considered greater than a million. Of about 4000 rows between a third and a half are blank (no count). I need the maximum values/count at the top of the list, but Excel puts these empty cells at the top of the list. As a result I have to do a lot of moving of large groups of data around to put the empty/blank cells at the bottom of these lists -- a real time consuming pain. Is there a way to have Excel treat empty/blank cells as having lower values than cells with values? I assume I could f...

Converting excel 2007 to tab delimeter text file
when I convert excel 2007 data into tab delimeter text file, it is adding two extra tab spaces at the end of each row, but it is not happending the same while converting excel 2003 into tab delimeter text file. please suggest me on this. Naresh, I don't think Excel 2007 adds any extra tabs. There might be a value in one of the cells somewhere beyond the columns with your data. Select 4-5 columns to the right of your data and clear them before saving in txt format. Did you test with the same file in both 2007 and 2003? "Naresh kumar" wrote: > when I...

Subtotal GroupBy text string
Hi, Is there anyway to use excel vba codes in a worksheet of about 20 or 3,000 rows or more ( ie dynamic rows and can be no row at all ), that can subtotal column B to N for each group of text string in column A which has met full or partial similar text. The Subtotal will insert after each grouping in every Section ( total 6 sections ) The results will display subtotal with each grouping of similar text string that fully or partially matched the text string within the same group together with grand total ( ie include subtotal ) E.g. ColA ColB ...

Text to time convert
How can I convert text to time format in cell. i.e text in cell A1 is 163233 - 16 is hour, 32 is min and 33 is sec. I need convert this text to time format: 16:32:33 Thanks for help, Rudo On Sun, 13 Nov 2005 10:54:01 -0800, "Rudo" <Rudo@discussions.microsoft.com> wrote: >How can I convert text to time format in cell. > >i.e text in cell A1 is 163233 - 16 is hour, 32 is min and 33 is sec. >I need convert this text to time format: 16:32:33 > >Thanks for help, Rudo =--TEXT(A1,"00\:00\:00") Format the cell as [hh]:mm:ss (or similar) --ron If y...

Count down to Words / Text
Hello, Is there a way to have a cell that is counting down from "10" and when it hits "0" spell the word FULL and in Red? example: cell BB10 has a formula in it: =COUNTA(H10:AV10) cell BB8 has a formula in it: =SUM(10-BB10) NOTE: there are only 10 entities max in cells H10 through AV10 and that is why I subtract BB10 from 10. I want cell BB8 to reflect the word ALL when there is 10 and FULL when the countdown hits "0" Can this be done? Thanks, Champ Try this....... =IF(SUM(10-BB10)=10,"ALL",IF(SUM(10-BB10)=0,&qu...

Filter on subform using unbound text box
Hello Forum Members: I'd like to have some help on the following codes on the Access 2000: On the Form Header, I have an unbound text box called FindWord, in which I type some words to search. The code below works on the main form. {code} Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.FindWord) Then strWhere = strWhere & "([English] Like ""*" & Me.FindWord & "*"") AND " End If lngLen = Len(strWhere) - 5 If lngLen <= 0 Then MsgBox "Nothing is speci...

not show value
Hi, I am creating a simple sheet that has repeated formulas in the columns. Maybe like this: Cell C1 =A1+B1 where B cells are a user input. Cell A2 will equal Value in C1 and so on. A B C 1 1 2 2 1 3 3 3 3 3 3 3 3 Is there a way to create incremental values in cells where no value will show until the B cell has had a value placed in it? I am trying to avoid a situation where the last value is repeated through out the columns as in 3 in cells C3:C5. Thanks, Louis Hi! Enter this formula in C1: =IF(B1="","",A1+B...

Text Wrap in OL 2002
Hi can I control the text wrap in OL 2002 WIN XP? I use Word as the email editor and use html format. Tx a lot. S ...

Text box in a Word template
I want to insert a text box from the forms toolbar into a template (.dot) file. When I do this, I unprotect my document and add the text field. Then I protect the document again. This is OK execept there is nothing to indicate to the user what to type into that field; it is just blank until the user clicks there. I would like to provide the user with some indication of what to type in the field-- for example [enter date here]. Once he type there the message should get replaced with what he types. Any ideas? Thanks, Stanley You can type default text into a text form field. Dou...

Cumulative column of values in SmartList Builder
I have a column that has numeric values and I want to create another column that adds and accumulate these values. For example: col 1 col 2 12 12 31 43 5 48 ....... It wanted to know like doing this with SmartList Builder. Thanks. You would probably need to create a SQL view and base the SmartList on the view. -- Charles Allen, MVP "Alejandro Luft" wrote: &g...

How convert Numeric number in words
Is there any easy formula or way to convert numeric values in words? For example in cell A1 there is an amount 19549. How could it will be automaticaly converted by a formula in cell B1 as Ninteen thousand five hundred and forty-nine. Please give me an easy answer because i am not a programmer but just an excel user. Imran Murtaza There is no direct function to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob "...

Is it possible to hijack text box functionality for custom control
I am working on a custom control, an extension of Panel which draws a series of rectangles in a flow chart. Each rectangle has a title and the ideal approach would be to allow the user to edit the title by simply clicking it. Obviously I could hook up a rename dialog to a context menu which would be easy but it is a bit more tedious to the users so I am hoping I can accomplish this the ideal way. Textbox functionality does not look simple though so I'd hate to recreate it for my control. Yeah handling the keyboard events to show what the user types is not hard but I am no...

Continuall Add ing values in a cell
Hi Everyone- I suspect that this question has been asked before but it is difficult to know how to find the correct thread. That being said, here it goes. I would like to make a cell continually add values entered into them. For example, I have a cell that has a value of $12.37 and I have to add to it a new value as I encounter it, lets say $8.73. As it stands know, I pull out my trusty calculator andsum up the two values and key in the sum into the field. I have a pile of recites that I need to add as they come up. Any help is greatly appreciated. Manuel A. Ayala CAD Concepts...

How do I match 2 items in excel to return a unique value?
For example, how do I match product codes and warehouses to return an unique price? It is a lot easier to answer a question like that if the rows and columnns data is provided. After all, nobody on this side of the server can see your worksheet. "Stumped" <Stumped@discussions.microsoft.com> wrote in message news:DF1EE951-D4F8-48AA-8040-4305FD948EB5@microsoft.com... > For example, how do I match product codes and warehouses to return an > unique > price? You are way to vague with your post. We need more details of what you are trying to ac...

publisher 2003 text wrapping
I just installed Publisher 2003 and cannot find the text wrapping tool in the format-layout tab. What's wrong? ...

Text as Pivot Data Field
Excel defines pivot a data field as "usually contains numeric data, such as statistics or sales amounts, but it can also contain text. Data from a data field is summarized in the data area of a PivotTable or PivotChart report." I have a Pivot Table containing: 1 Row Field called: Name 1 Column Field called: Date 3 Data Field (2 numeric, 1 Text) Everything comes in great except the text field is zero. I'm guessing the reason is because the Pivot is forcing me to perform a "Sumarize As" function like count, sum, ... How can i get the text in too? Is there a wa...

Best way to find the last value in a variable-length table?
I have a table containing a series of readings, something like this: A B # Volume 15 1 1,000 ml 16 2 915 ml 17 3 830 ml 18 4 745 ml 19 5 660 ml ... I need a way to find the last row of the table, which could have anywhere from 2 to 20 rows. hi, ! if column A has only numbers - a formula outside column A =match(9e307,a:a) - a formula outisde "the range" =match(9e307,a1:a20) hth, hector. __ OP __ > I have a table containing a series of readings, something like this: > A B > # Volume > 15 1 1,000 ml &...

"frequently used text fields" as default search in advanced find?
I never search just the subject field in Outlook's advanced find. I always search "frequently used text fields". I dont want to have to think about which part of an email to search - I just want to find the word or phrase I have in mind. I have years worth of emails, and the search speed when searching every field is acceptable. There's no need to restrict the search to just one field. It's counter productive to have to stop and select this option every_single_time I use advanced find. It drives me crazy. Any way to set this as default? ...

Script running other scripts return values
hello I've got series of VB scripts that I run manually buy clicking on them, Can't I have one script that runs all of them? I would want to check a returned variable to is one script has a failure, the later one won't get executed. Can't I just use the .Run method? How do I return a value from one script, and read it in the other script? Thanks "Bill" <someplace@somewhere.com> wrote in message news:e72gZtg7KHA.5644@TK2MSFTNGP04.phx.gbl... > hello > > I've got series of VB scripts that I run manually buy clicking on...

Plain-text font size
This is a multi-part message in MIME format. ------=_NextPart_000_0097_01C35743.0FF78190 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Is there any way to increase the font size for reading e-mail from your = in-box? --=20 Janan Zonker California State University, Fullerton Faculty Affairs and Records O - 714-278-7420 F - 714-278-7597 jzonker@fullerton.edu No trees were harmed in the transmission of this message; however, a large number of electrons were temporarily inconvenienced. ------=_NextPart_000_0097_01C35743.0FF78190 Content-...