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

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

=IF(AND(ISNUMBER(P821),ISNUMBER(R821),ISNUMBER(Q82 1),ISNUMBER(S821)),(Q821-P821)+(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. The
planned on-actual on + planned off-actual off = hours lost. but thi
will not take into account that the planned time may be before midnigh
and the actual might be after midnight if they started late.
It then gives ###### in the cell because it thinks time has bee
gained. So I would like to show time lost as -hh:mm and time gained a
+hh:hh in the cell if this is possible, as well as calculating the los
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 cel
format for the hours lost is hh:mm.

Thank

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