Thread: #Value error

Hi All
Office excel 2003
got a times worked sheet but if text entered in a cell #Value error appears & destroys formular can I add to formular to ignore text in cells & only count time please find attached
sheet starts monday to sunday start & finish times with 1/2 hour deducted for lunch but need to fill in when someone on sick course or holday these are in a drop down list

Many thanks

before we go there what is this bit supposed to be doing?
IF(D5>E5+F5>G5+H5>I5+J5>K5+L5>M5+N5>O5+P5>Q5,0)

Hi,
Use ISTEXT or ISNUMBER function.
Look at attached sheet and notice I added a collumb for hours worked for Monday.You can do that for every day and ad all the hours for the week in the last col.
You can do it all in one formula in the last col.,but as you can see, it will be a very long and complicated formula.
Be sure to format the "Hours" collumb as number, else your result could be in "Time of day" instead of hours.

Regards
Dan

I think the version you have is incorrect (regardless of text) assuming you wish to deduct 30 minutes per day.

If the shifts never cross midnight then the below should suffice:

R5:
=SUM(SUMIF($D$4:$Q$4,{"TIME OUT","TIME IN"},$D5:$Q5)*{1,-1})-COUNT($D5:$Q5)/2*"0:30"
copied down
format as [hh]:mm

If shifts do cross midnight you can use a SUMPRODUCT approach with MOD (though an Array may prove to be preferable given Text etc...)