Rota Spreadsheet

[nigelandrewfoster]

I have a simple rota spreadsheet which calculates hours worked per worker MONDAY to FRIDAY by inputting START and FINISHING times for each day. It works, until the user inputs "HOLIDAY" or other text into the table, which results in "#VALUE!" in the totals box. Is there any way I can get Excel to ignore text or treat it as zero without resorting to a macro?

 

[bobsan42]

what is the formula which calculates the total?

 

[nigelandrewfoster]

=(C4-B4)+(E4-D4)+(G4-F4)+(I4-H4)+(K4-J4)+(M4-L4)+(O4-N4) IN Q4, THEN =IF(Q4>0,Q4,0) IN P4 (TO SCREEN OUT NEGATIVES)
Thanks,
Nigel

 

[bobsan42]

then i guess you should put an error check ot each subtraction (sorry but can't think of a more elegant way). The question is what shgould happen if there is Holiday - shoud it return 0? if yes then try this:
=if(iserror(C4-B4),0,(C4-B4))+if(iserror(E4-D4),0,(E4-D4))+if(iserror(G4-F4),0,(G4-F4))+if(iserror(I4-H4),0,(I4-H4))+if(iserror(K4-J4),0,(K4-J4))+if(iserror(M4-L4),0,(M4-L4))+if(iserror(O4-N4),0,(O4-N4))

 

[nigelandrewfoster]

Thanks for that. Never used 'iserror' before. I'm sure it will be very useful in the future.

 

[Toonies]

Hi I have a Rota Spreadsheet that will help you out.

If you PM me I will give you more detail.

Many Thanks

Toonies