elapse time function ( time entered with no colon).

[jackm1249]

Hi, Newbie here.

I have seen some other threads on this, but didn't want to ask in those...

I have been entering some backup times using 24 hr clock, and manually entering the colon, example:

B2 22:10 C2 22:50 with results in D2 0:40

But now I have a project that I will be entering about 100 of these a day ( 24 hour format) And I would like not to have to put in the colon.

In other threads I found the use of Custom 0\:00 format allows me not to enter the colon. cool/ but I am no longer getting correct results in D2

A few of these times will go over the midnight mark, so results would have to calculate that also.

I hope I explained this correctly.

[ChemistB]

In D2 copied down

=MOD((LEFT(C2,LEN(C2)-2)&":"&RIGHT(C2,2))-(LEFT(B2,LEN(B2)-2)&":"&RIGHT(B2,2)),1)
Does that work for you?

[Jonmo1]

Here's another way (assuming the times are hh:mm, not mm:ss)

=MOD(TEXT(C2,"00\:00")-TEXT(B2,"00\:00"),1)

[jackm1249]

In D2 copied down

=MOD((LEFT(C2,LEN(C2)-2)&":"&RIGHT(C2,2))-(LEFT(B2,LEN(B2)-2)&":"&RIGHT(B2,2)),1)
Does that work for you?

Thanks for the speedy reply.

It does work, if both times are before midnight. Issue I am having is if say.. Backup starts at 22:00 and ends at 3:00

[jackm1249]

Here's another way (assuming the times are hh:mm, not mm:ss)

=MOD(TEXT(C2,"00\:00")-TEXT(B2,"00\:00"),1)

Thanks, That works.

[ChemistB]

That works for me even across Midnight. Jonmo's formula is better though.

[Jonmo1]

I also get the same results with both formulas.

Make sure you have the cell formatted as a time, not number


Anyway, glad you got it sorted.