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.

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.

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.