Time

[ryan.katy]

Hi guys,

Wondering if you can help me. I'm trying to calculate the difference between two times. EG

H2 = 12.35
I2 = 13.10.

When I had the formula I2-H2 with the format of J2 as General, I got the followinf answer: 0.75, which is the serial number. However, when i change the format of the cell to h:mm, which is how I want it to be displayed, I'm getting 18.00 as the answer??!!

The formats of H2 and I2 are Custom: 00.00
It just doesn't make sense to me.

Thanks a million
Kat

[daddylonglegs]

The easiest option would be to have all cells formatted as times, and enter them with colons, i.e. 12:35 and 13:10, then use formula

=I2-H2

for an answer of 0:35

[J2 formatted as h:mm]

[se1429]

Hi,

You have to convert your custom format to standard time format before calculation. Assuming that your times are in A1 and B1 respectively, use the following formula in the cell where you want the result and format it as time "hh:mm":

Please Login or Register  to view this content.

Regards.

[daddylonglegs]

=TIME(LEFT(B1,2),RIGHT(B1,2),"00")-TIME(LEFT(A1,2),RIGHT(A1,2),"00")

.....but this won't work for the example given because with a time like 13.10 the zero is only formatting, RIGHT(B1,2) will be equal to ".1) and you'll get the wrong result.

As per my post above. Using recognisable times will always make all of your calculations easier. If you do want to use the current formatting then to return a time value like 0:35 try this formula in J2

=TEXT(I2*100,"00\:00")-TEXT(H2*100,"00\:00")

format J2 as h:mm

...or if you want to return 00.35

=TEXT(TEXT(I2*100,"00\:00")-TEXT(H2*100,"00\:00"),"h.mm")+0

format J2 as 00.00

[ryan.katy]

Thanks a million for those replies. Daddylonlegs, I tried your first post and it's working perfectly. If ye don't mind, I'd like to pick your brains once more.

In the case of the end time going into the next day.....how exactly would that work?

EG

H1: 22.50...I1: 00.10

I assume I'd have to add 24 or perhaps a value of one to the formula??

[oldchippy]

Hi,

May be

=IF(H1>I1,TEXT(I1*100,"00\:00")-TEXT(H1*100,"00\:00")+"24:00:00",TEXT(I1*100,"00\:00")-TEXT(H1*100,"00\:00"))

[daddylonglegs]

If you're using real times like 22:50 and 00:10 then you can just use

=MOD(I1-H1,1)

or if you have 22.50 and 00.10 then try

=MOD(TEXT(I1*100,"00\:00")-TEXT(H1*100,"00\:00"),1)

In either case format result cell as h:mm

[ryan.katy]

Hey guys.

Thanks for all you help!