Difference between 2 times (24 hours)

[csynic]

Hi everyone,

I've been trying some of the code here to solve the problem i have, but i can't seem to make it work. (i'm pretty new to this)

i have 2 sets of times in 24 hour

start time (F1), end time (G1)
810 940

i want to create a new column which gives me the difference in minutes between these times. it should account for times spanning 0000 (12am)

the new column should read 90. is it possible to do this, without having to recompute the times above to 8:10 and 9:40?

thanks for the help!

[Colin Legg]

If the times span different dates then you need to include the dates in the cells.

So:

Please Login or Register  to view this content.

Then you can just use this simple formula:

=G1-F1

and format the cell containing this formula as [mm]

[csynic]

what if i dont change the times in my cells to include dates? (lets assume they don't span 12am for now) how would it be done?

just as a learning point, i just entered cell info above, and tried a =G1-F1 but the cell returned #value. what do you mean by format the cell according to [mm]?

i'm pretty new to this, thanks for the patience!

[Roger Govier]

Hi

If you are using straight numbers and not Excel time values then the following formula will give your answer, even allowing for times crossing the 24 hour barrier.

Please Login or Register  to view this content.

[snb]

or

=TEXT(TIMESERIAL(LEFT(G1,LEN(G1)-2),RIGHT(G1,2);0)-TIMESERIAL(LEFT(F1,LEN(F1)-2),RIGHT(F1,2),0),"[mm]")  

[daddylonglegs]

Try this formula

=MOD(TEXT(G1,"00\:00")-TEXT(F1,"00\:00"),1)*1440

[snb]

I did, so this will do:

=(TEXT(G1,"00\:00")-TEXT(F1,"00\:00"))*1440  

or

=text(TEXT(G1,"00\:00")-TEXT(F1,"00\:00"),"[mm]")  

[daddylonglegs]

....it should account for times spanning 0000 (12am)

Given the above I think you need to use MOD function otherwise you will get incorrect values when the times cross midnight, e.g. 2220 to 200 will give you -1220 rather than 220

[csynic]

hey everyone thanks for the help, the mod function seems to work best. just so i understand what the code is doing, what does 00\:00 mean? (i only get that it is some kind of text format)

suppose now i have a list of times, for example 0024, 0030, 0100 (24 min, 30 min, 100 min) and their cell format is general, i tried to sum them up, but i get 00:00. how can i get 0154 minutes? the 0 in front wont be an issue when working it into other formulas will it?

[snb]

Notavi bene !

[daddylonglegs]

The formula I suggested uses the TEXT function to convert a value like 200 into a time value like 02:00, then you can just process the values like you would times anywhere else.

You can do the same to sum the values, e.g. if you have data in that format in A2:A10 you can use this formula to sum

=TEXT(SUMPRODUCT(TEXT(A2:A10,"00\:00")+0),"hhmm")

That's a text value so it could be problematic in other formulas (it depends on what you do, exactly, you'll still probably have to convert it to a time)

You could make it a numeric value that looks like 0154 by taking out the first TEXT function, i.e.

=SUMPRODUCT(TEXT(A2:A10,"00\:00")+0)

That will give the result 154 and it will be used in calculations as that value if you use the cell in other formulas. Make it look like 0154 by custom formatting the cell as 0000

[csynic]

thanks daddylonglegs, could i find out what the forward slash in the 00\:00 does in the formula? just running through stepwise, my raw data is in the form 0200, then it turns into 02:00, then it turns it into a text form of 02:00?

[Leith Ross]

Hello csynic,

Since the colon is used in formatting time values, Excel will misunderstand your intentions when formatting the number as text. Rather than convert the number into the string: number character; number character; colon; number character; number character, Excel will try to convert the number into a time value (decimal fraction).

The backslash (\) is added to tell Excel the colon is a regular character, and has no special meaning. Now, the number will be converted to a text string like: number character; number character; colon; number character; number character. The result is the number looks like a time value but is actually just text.