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

Hi Neowok,

Try using the Custom format:

[hh]:mm


---
Regards,
norman




"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/

ok ill have a look at that (think ive got the formula worked out now.

whats the difference between [hh]:mm and hh:mm ? im guessing [hh] wil
accept negative values

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

=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/

You can display negative time unless you change your setting in the
Calculation Tab to use the 1904 time system (default in the MAC). This will
change any existing dates in any workbook you open to be off by 4 years (if
they used the other system).

[hh] will allow excel to display time in excess of 24 hours. (but I doubt
it solves your current problem).

--
Regards,
Tom Ogilvy



"neowok " wrote in message
...
ok ill have a look at that (think ive got the formula worked out now.

whats the difference between [hh]:mm and hh:mm ? im guessing [hh] will
accept negative values?


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

Hi Neowork,

My apologies, I mis-read your initial post.

whats the difference between [hh]:mm and hh:mm ? im guessing [hh] will
accept negative values?


The custom format allows times to be displayed without overflowing into
days and therefore .enables the display of times greater than 24 hours.


---
Regards,
Norman

can should be can't
as in :

You can't display negative time unless

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
You can display negative time unless you change your setting in the
Calculation Tab to use the 1904 time system (default in the MAC). This
will
change any existing dates in any workbook you open to be off by 4 years
(if
they used the other system).

[hh] will allow excel to display time in excess of 24 hours. (but I
doubt
it solves your current problem).

--
Regards,
Tom Ogilvy



"neowok " wrote in message
...
ok ill have a look at that (think ive got the formula worked out now.

whats the difference between [hh]:mm and hh:mm ? im guessing [hh] will
accept negative values?


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

ok, i modified my formula to correctly catch instances where the planne
might be before midnight and the actual might be after using

=IF(AND(P50.791666,Q5<0.1875),(Q5-P5+1)+(R5-S5),(Q5-P5)+(R5-S5))

the 0.791666 is equivalent to 19:00 and the 0.1875 is 04:30, basicall
if planned start is after 7pm, and actual start is before 4.30am the
add 24 hours to the result, else both planned and actual are eithe
before or after midnight and so calculate normally. This seems to b
catching all instances of it.

I wont bother with the negative time for now

Thank

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