Time calculations

Been here at my desk for two days trying to find a formula that will calculate the difference between two times. I've tried multiple formulas and been online for hours and can't get one formula that will work for all. Anybody have a minute to take a look, please and thanks?

I have attempted to attach file with formula. Check it out and reply. thx

in cell C5 place the following formula: =IF(A5>B5,TEXT(A5-B5,"h:mm:ss")&" Before",TEXT(B5-A5,"h:mm:ss")) . The "Before" stands for 'actually departed BEFORE the Scheduled departure. (Please note, your dates are not date formats....they are text (the above formula will work). Reminder: please post whether or not this had assisted you. thx

As queuesef pointed out above, your problem is that the dates/times you have are actually text, not dates/times as Excel understands them. The easiest way to deal with this is to first convert them to actual dates/times and then to do the calculation. To do this, add two 'helper' columns - insert new columns C and D between your current columns B and C. Then put this in the new C2:

Formula:

=DATE(--MID(A2,7,4),--LEFT(A2,2),--MID(A2,4,2))+TIME(--MID(A2,12,2),--RIGHT(A2,2),0)

Then this in the new D2:

Formula:

=IFERROR(DATE(--MID(B2,7,4),--LEFT(B2,2),--MID(B2,4,2))+TIME(--MID(B2,12,2),--RIGHT(B2,2),0),"")

Drag down both columns.
Note that both formulae assume that the text-formatted dates/times are always formatted as mm/dd/yyyy[space]hh:mm - in other words, with two-digit months and days, and using the American mm/dd format not the international dd/mm format.

Now the calculation in Col E (your old Col C) is fairly simple - subtract one time from the other, using an IF formula to check which time is later. The formula below will return just the time difference if the actual departure is later or equal to the scheduled, and will add ' early' if the actual time is earlier. Format the column with the custom number format [h]:mm.

Formula:

=IFERROR(IF(D2>=C2,D2-C2,TEXT(C2-D2,"[h]:mm")&" early"),"")

Note that the formula above changes the figure to text if the dep. was early, so if you want to do calculations with the numbers, it might be better to just have the number (i.e. the time diff) and put another column to note whether the actual dep is earlier or later. Put these two formulae in E2 and F2, then drag down.

Formula:

=IFERROR(IF(D2>=C2,D2-C2,C2-D2),"")

=IF(D2="","",IF(D2=C2,"on time",IF(D2>C2,"late","early")))

Hope that helps. Attached is a file showing it working - two sheets, with the two options for showing late/early.

Hi,

Although OP's Columns A and B and Text values and Not Real Date/Time values, once we do arithmetic with the data, it will automatically convert to Real Date/Time values.
Since OP did not specify How he/she wants the results, I've included 2 versions:

If you Only want to see the Time difference, use this in C2 copied down, format Column C custom [h]:mm:ss

Formula:

=IF(B2="","",ABS(B2-A2))

Then, use this in D2 copied down to indicate "On Time", "Early", or "Late" if desired:

Formula:

=IF(B2="","",IF(B2>A2," Late",IF(B2<A2," Early"," On Time")))

If you want the time difference And the indicator whether it was "On Time", "Early", or "Late" all in a Single cell, then use this in C2 copied down, Column C then formatted General

Formula:

=IF(B2<>"",TEXT(ABS(B2-A2),"[h]:mm:ss")&IF(B2>A2," Late",IF(B2<A2," Early"," On Time")),"")

No helper columns needed, see attached.

Edit: added extra formula option.

Hi jtakw. Thanks for popping in. Perfect example of why I "hate" Excel. Sometimes it doesn't make sense. Your spreadsheet is great. Thank you. But when I apply the formula to the spreadsheet I attached I get #value errors. And col C is formatted as custom [h]:mm:ss

And even when I copy your formula (which works) into another col. I still get the #value errors. And again it is custom [h]:mm:ss.

Put a 1 in D1, copy
Select A5:C42, Pastespecial, multiply, format as "hh:mm:ss"
C5: =ABS(B5-A5)

Jtakw. But when I use your formulas in another workbook, they work. Too tired to figure this out tonight. Thanks so much for your help. I'll be back at 'er in the early am. Might be back here... lol

I get #Value errors with all the suggested solutions as well - that's why I went the long-winded helper columns & conversion route in post 4. I'm guessing the problems are something to do with Regional Settings, as it's the members from outwith the USA who seem to be getting #Value errors whilst those from the USA aren't.

Assuming your settings use dd/mm/yyyy date format, it would be very dangerous to use simple subtractions on text values. For example, the difference between 10/09/2018 23:45 and 10/10/2018 00:04 is almost 700 hours with UK settings, which I suspect is not the intention. And of course, when the day number goes over 12, you'll just see #VALUE! errors.