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.