Time Calculations

[Steve Parry]

Hi guys,

I am trying to perform a time calculation on two cells. I
have a start time and an end time. I subtract the start
time from the end time to give me duration. The format of
the cells is hh:mm:ss. This works fine except where the
end time is past midnight (i.e. 00:13:15). Can anyone help
with why it does that or how to fix it?

Cheers guys.

Steve.

 

[Govind]

Hi,

Use this formula.If you have End time in B1 and start time in A1, then
use this formula

=IF(B1>A1,B1-A1,B1+24-A1)

and format the cell to hh:mm:ss.

Govind.

 

[Gary Thomson]

Hi,

Here is a way to get around it:

I am assuming your first set of times are in column A
(i.e. A1, A2, A3, etc); and your second set of times are
in column B (i.e. B1, B2, B3, etc); and that you want the
difference displayed in the next column.

What you actually need to do in insert another column,
Column C, which has the following formula in cell C1:

=IF((B1-A1)<0,HOUR(B1)+24&":"&IF(MINUTE(B1)<10,"0"&MINUTE
(B1),MINUTE(B1)),"Ok")


Now, copy the formula down through column C.

Now, put the following formula in cell D1:

=if(C1="Ok",B1-A1,C1-A1).

This should get around the "midnight problem".

Happy to Help,

Gary Thomson.

 

[Harald Staff]

Have to disagree with you here, Govind. Your formula adds 24 days to the end
time, not 24 hours. Time may display fine in the cell, but real content will
be 552 hours too many.

=IF(B1>A1,B1-A1,B1+1-A1)
or
=B1-A1+(B1<A1)

HTH. Best wishes Harald

 

[Govind]

Hi Harald,

Thanks for the hint.

Regards

Govind.