How do I assign a numeric value to a text letter

[Guest]

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.

 

[Guest]

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

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk