We help IT Professionals succeed at work.

Excel time formula based on mins

vegas86 asked

Can someone please help me with the following?
I have a spreadsheet that documents whether a train is running early or late. I would like a formula to calculate the difference in minutes and put either an E or L next to the mins.
I have attached a spreadsheet as an example. I currently have a formula in column C that shows the mins but I would like to also include an E or L so I can determine who was early and who was late.

Any help would be appreciated!
Watch Question

Try this

=IF(A2>B2,CONCATENATE(TEXT(A2-B2,"h:mm"), "E"),CONCATENATE(TEXT(B2-A2,"h:mm"), "L"))
Although I know this doesn't address your question directly, you are going to face some problems with your formula in either the move from AM to PM (if you don't have 24 hour data) and/or the move from PM to AM.  If you strictly have times with no date attached, then the problem becomes a little tricky - exactly how late can a train be?  What if the expected time was 11:00 and it's arrival was at 10:50?  Was it 10 minutes early, 23 hours and 50 minutes late (or even more), or in the absence of a 24 hour-based timestamp, was it 11hr 50min late?

Just something to consider.

AgeOfEmpires, I looked in the posted example and it is an AM/PM time so it is 24hr. He should be ok unless the 2 times straddle midnight. In that case, you are correct and a full date/time stamp is needed.
Just wanted vegas86 to be aware.  Any solution needs to incorporate a check for eclisping midnight, if the date is available.  Otherwise, absent the date, it will be a little tricky, unless, as I offered, you can make some reasonable assumption about exactly how late is too late for a train to show up.  I guess that depends on where you live!  :)


Thank you Lee555j5 for your help and also thanks Ageofempires for your input, much appreciated!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.