=IF(AND(ISNUMBER(P821),ISNUMBER(R821),ISNUMBER(Q82 1),ISNUMBER(S821)),((Q821-
P821)+(P821Q821))*24+((R821-S821)+(S821R821))*24,"N/A")

format as General.

--
Regards,
Tom Ogilvy


"neowok " wrote in message
...
I have a spreadsheet with the collowing columns in P,Q,R,S - planned
time on, actual time on, planned time off, actual time off. and in
column U I have Hours Lost

I've been trying to create a formula which will correctly calculate the
hours lost. The problem is taking into account that sometimes the
planned time on can be before midnight, and the actual time on may be
after midnight so the formula is failing because its not calculating
this properly. The formula I currently have which works other than
this is


=IF(AND(ISNUMBER(P821),ISNUMBER(R821),ISNUMBER(Q82 1),ISNUMBER(S821)),(Q821-P
821)+(R821-S821),"N/A")

It first checks that each of the 4 columns ALL contain a number (i.e.
they arent blank or saying N/A), if they dont then set it to N/A. Then
planned on-actual on + planned off-actual off = hours lost. but this
will not take into account that the planned time may be before midnight
and the actual might be after midnight if they started late.
It then gives ###### in the cell because it thinks time has been
gained. So I would like to show time lost as -hh:mm and time gained as
+hh:hh in the cell if this is possible, as well as calculating the lost
hours properly.

an example of the problem would be P2=23:30, Q2=01:20, R2=04:45,
S2=04:45. the formula then thinks it has gained hours which is wrong,
and also doesnt show the number of hours and mins gained. The cell
format for the hours lost is hh:mm.

Thanks


---
Message posted from http://www.ExcelForum.com/