Formula to calculate duration

[Jomejorada]

Hi,

I would to calculate the duration but the data I have has a different format than what I'm used to. Please see attached.

Thank you in advance for your time and assistance.
Jocelyn

[DannyJ]

I think this is what you want? I had to change a few formats for it.

Change the times in columns C and D to include :

Then the formula is - =SUM(RIGHT(D2,4)-RIGHT(C2,4))

[FDibbins]

Im sure there is a simpler way to do this, but try...
=(DATEVALUE(LEFT(D2,10))+TIMEVALUE(MID(D2,12,2)&":"&RIGHT(D2,4)))-(DATEVALUE(LEFT(C2,10))+TIMEVALUE(MID(C2,12,2)&":"&RIGHT(C2,4)))
copied down

edit: Correction...
=(DATEVALUE(LEFT(D2,10))+TIMEVALUE(MID(D2,12,2)&":"&RIGHT(D2,2)))-(DATEVALUE(LEFT(C2,10))+TIMEVALUE(MID(C2,12,2)&":"&RIGHT(C2,2)))

[FDibbins]

If the dates will always be teh same, here is a shorter version...
=TIMEVALUE(MID(D2,12,2)&":"&RIGHT(D2,2))-TIMEVALUE(MID(C2,12,2)&":"&RIGHT(C2,2))

Note that in your last row, you have Pre-op before In fac?

@ Danny, your formula doesnt always work, check the 2nd row. Duration is 29 minutes, but your formula shows 69?

[DannyJ]

It works fine when I test it. All you need to do is format it to custom ([h]:mm or [m]) or time and it shows as 00:29. If dates are going to be different then obviously it won't work. I worked on the assumption it would always be the same date.

[Phuocam]

Another way ...

=TEXT(RIGHT(D2,4),"00\:00")-TEXT(RIGHT(C2,4),"00\:00")