Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 Replace@mailinator with @tiscali.co.uk "Shaun" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to numeric vba add-in? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions |