Subtracting military time formatted as text

I would like to calculate the time difference between military time formatted as text without converting the times to numerical values - with the output in standard hours/minutes. Is this doable?

NEWDELTIME OLDDELTIME HRSVARIANCE Comments
2145 2100 45 Correct - the time difference should be 45 min
2315 2230 85 Incorrect - the time difference should be 45 min
0030 2345 2315 Incorrect - the time difference should be 45 min

Try

=MOD(TEXT(A2,"00\:00")-TEXT(B2,"00\:00"),1)*1440

Generally "Anything may be possible" however, to actually do it may require a LOT of work! Generally, I would use military time as Decimal values...so in your examples, I would use 21.75 for 2145; 23.25 for 2315; .5 for 0030 and 23.75 for 2345. You will still need to make allowances for times not on the same DAY as in YOUR last example (perhaps add 25 hours to 0030??), but calculations would be much smoother!

Thanks, the calculation works most of the time.

You're welcome.

Can you post examples of times it doesn't work, Including the results you expected ?

NEWDELDAY NEWDELTIME OLDDELDAY OLDDELTIME New Formula Hrs
MON 2015 MON 2200 1335 22.25
WED 0630 WED 0900 1290 21.5
WED 0345 WED 0730 1215 20.25
MON 2315 MON 2100 135 2.25
MON 2215 TUE 0030 1305 21.75
(see attachment)

And what's the problem?
Which cells are not returning the value you expected ? what was the value you expected ?

Do you want the result to be shown in an actual time format ?
Remove the *1440 and format the cell with the formula as a time hh:mm

See attached. There looks like some issues with your data (no punch out or NA) and you need to ASSUME if the Punch out time is Less than Punched in time to compensate (Example: orig Punch in 11:00pm or 2300 hours with Punch out on next day at 9:00am or 0900 ~ Add 24 hours to the In time: (0900 + 2400)- 2300 = 1000 or 10 HOURS.

I see the mistake now.

In your original post, you had the times listed as
LaterTime EarlierTime

So I wrote it as A-B (LaterTime-EarlierTime)


But in the book you posted, you have the times as
EarlierTime LaterTime
But you still did A-B (or B-D), which ended up as EarlierTime-LaterTime

You need to reverse the math to D-B

=MOD(TEXT(D2,"00\:00")-TEXT(B2,"00\:00"),1)*1440

And again, remove the *1440 and format the cell as TIME if you want it shown in a real time formulat hh:mm

Thanks, but the time is neither older or later - just different. I want to know the difference in time. It looks as though if the new time is later than the old time the formula is B-D. If the new time is earlier than the old time the formula is D-B. So now I need an IF statement. Oh wow!

There is always an earlier and later when comparing times.
Maybe it would be clearer to say which time occurred first ?? The one in B or the one in D ?

Your time stamps must relate to something, like a Check in and a Check out time..
One of them happened first.

Think about a watch.

Is there 4 or 8 hours between 10 and 2

Either is correct. It depends on which occurred first.
If you're measuring time from 10 to 2, then it's 4.
If you're measuring time from 2 to 10, then it's 8.

The times are routing changes in ETAs.

The old time is the current ETA. The new time will be the new ETA.

So my question is...

Are you trying to measure how much time is from B to D, or from D to B ??

D to B - old time to new time

USE A DATABASE!! Or use standard formatting conventions

D to B, Old time to New time.

OK, so it needs to be B-D then.

=MOD(TEXT(B8,"00\:00")-TEXT(D8,"00\:00"),1)*1440

You said that works 'most of the time'

Then you posted a new book showing the formula I suggested
Which cells in the book you posted in Post #6 were not correct ? And what should the correct answer have been ?

Or use standard formatting conventions

Yes, This.


I'm now seeing huge problems...
We need to consider the DAY as well.

Take Row 13 and 18 (in the book you posted in post #6)
Row 13 A = Mon C = Tue Is the Difference here Mon Tue, or Tue Wed Thur Fri Sat Sun Mon ??
Row 18 A = Wed C = Tue Is the Difference here Wed Thu Fri Sat Sun Mon or Tue Wed ??


You need to use complete Date and Time Stamps.
Real Dates and Real Times.

Yes I know it's more key strokes to enter a date and time (with the
But it's worth it in the long run.

I am trying to measure how much time is from D to B (old time to new time)

See post #19

Take row 18 in the latest book just posted.

OLD ETA = TUESDAY 2330
NEW ETA = WEDNESDAY 0145

That formula gives 21 hours and 45 minutes. But that's only considering if both eta's are on the same day.
But since the NEW eta is on Wednesday, the difference is really only 2 hours and 15 minutes.