Two time values in a single cell, how to return difference?

Hi guys

I have a single cell in which a time window (in lack of a better word!) has been exported in text format, like this:

10:15-15:00

What I need as a result into another cell is the difference between those times in hours and minutes, in this case four hours and forty-five minutes (4:45)

The formula should work with times between 0:00-23:59, also with times that pass midnight (ex. 23:00-5:00 = 6.0 hours)

I wasn't able to figure out a combo that works. How could I accomplish this?

Not the neatest formula in the world, but with your string in cell A2 this should work:

=((TIMEVALUE(TRIM(LEFT(A2,FIND("-",A2)-1)))>TIMEVALUE(TRIM(MID(A2,FIND("-",A2)+1,255))))+TIMEVALUE(TRIM(MID(A2,FIND("-",A2)+1,255))))-TIMEVALUE(TRIM(LEFT(A2,FIND("-",A2)-1)))

Try this

=MOD(MID(A2,FIND("-",A2)+1,9)-LEFT(A2,FIND("-",A2)-1),1)

That gives me a value error when it tries to subtract one string from another, Bob, but this works:

=MOD(TIMEVALUE(MID(A2,FIND("-",A2)+1,255))-TIMEVALUE(LEFT(A2,FIND("-",A2)+1)),1)

Really, it works fine for me.

Actually, yours gives me an error, it should be

=MOD(TIMEVALUE(MID(A3,FIND("-",A3)+1,255))-TIMEVALUE(LEFT(A3,FIND("-",A3)-1)),1)

Wow, that's weird. You're right, it should be -1 rather than +1, but both work fine for me.

Oh well, I've had too many glasses of wine to worry about it - let's call it a draw

Got it - the difference is in if there are spaces before and after the dash - if there are then both versions of my formula work fine but yours doesn't, if there are not then your formula and the corrected version of mine work, but the uncorrected version doesn't.

As we haven't heard back from the OP the point is probably moot.