Calculate time difference between cells

[Guest]

I am trying to calculate the time difference between to cells. I have a
formula that works ok unless the difference is in excess of 24 hours. I am
using time data only in the cells 08:00, 18:00 etc..
current formula;
=IF(I29="","",IF(J28="","",(IF(J28<I29,I29-J28,(IF(J28>I29,24-J28+I29))))))

What I want to do:
column I column J column K column L
(formula above in L)
Clock-in Clock-out Total HRs worked hours off
between J2 & I3
1 08:00 20:00 12:00
[Blank] didn"t work day bfr
2 06:00 19:00 13:00 10
3 20:00 08:00 12:00 25

What I get in L3 now is 1.
Any Suggestions???

 

[Bob Phillips]

Format those cells as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to calculate the time difference between to cells. I have a
formula that works ok unless the difference is in excess of 24 hours. I am
using time data only in the cells 08:00, 18:00 etc..
current formula;

What I want to do:
column I column J column K column L
(formula above in L)
Clock-in Clock-out Total HRs worked hours off
between J2 & I3
1 08:00 20:00 12:00
[Blank] didn"t work day bfr
2 06:00 19:00 13:00 10
3 20:00 08:00 12:00 25

What I get in L3 now is 1.
Any Suggestions???

 

[Guest]

I tried that and it did not make a difference. The problem is with the
formula. When I calculate a time off between one day and the next, if the
time off is in excess of 24 hours it computes it as being on the same day and
returns a 1 for 25 hours off or a 2 for 26 etc...

BTW - the data in the cells is time only. I have some non-computer folks
that have a hard time remembering how to format date and time in a cell. Any
other suggestions would be greatly appreciated.
--
skyknight

Format those cells as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to calculate the time difference between to cells. I have a
formula that works ok unless the difference is in excess of 24 hours. I am
using time data only in the cells 08:00, 18:00 etc..
current formula;

What I want to do:
column I column J column K column L
(formula above in L)
Clock-in Clock-out Total HRs worked hours off
between J2 & I3
1 08:00 20:00 12:00
[Blank] didn"t work day bfr
2 06:00 19:00 13:00 10
3 20:00 08:00 12:00 25

What I get in L3 now is 1.
Any Suggestions???

 

[Bob Phillips]

If it is time only, how does it know it is more than one day?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I tried that and it did not make a difference. The problem is with the
formula. When I calculate a time off between one day and the next, if the
time off is in excess of 24 hours it computes it as being on the same day and
returns a 1 for 25 hours off or a 2 for 26 etc...

BTW - the data in the cells is time only. I have some non-computer folks
that have a hard time remembering how to format date and time in a cell. Any
other suggestions would be greatly appreciated.
--
skyknight

Format those cells as [h]:mm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am trying to calculate the time difference between to cells. I have a
formula that works ok unless the difference is in excess of 24 hours.

I
am

using time data only in the cells 08:00, 18:00 etc..
current formula;

What I want to do:
column I column J column K column L
(formula above in L)
Clock-in Clock-out Total HRs worked hours off
between J2 & I3
1 08:00 20:00 12:00
[Blank] didn"t work day bfr
2 06:00 19:00 13:00 10
3 20:00 08:00 12:00 25

What I get in L3 now is 1.
Any Suggestions???