Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need time calculation formula
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula For Time Duration Calculation | Excel Discussion (Misc queries) | |||
Time calculation or formula | Excel Discussion (Misc queries) | |||
create time calculation formula | Excel Discussion (Misc queries) | |||
formula date and time calculation | Excel Discussion (Misc queries) | |||
Time Calculation Formula! | Excel Discussion (Misc queries) |