Duration between Military Time

Duration between Military Time

What is the easiest formula to use to determine the time difference between two Military Times? I'm using the 4 digit military time and also I need the formula to calculate across midnight. I'd like the time difference to be in minutes (but that can be done in a separate column formula). I know @Paul Newcome has skills when it comes to calculating time.

Example: End time = 1450, Start time = 1225

Example: End time = 0041, Start time = 2125

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...

    Firs the formula to be able to use the minutes as a decimal...

    =VALUE(RIGHT([End Time]@row, 2)) / 60


    Then we need to add this to the hours...

    =VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60


    This will give you a number that represents how far into the day your End Time time is. Duplicate this for the Start time and subtract it from the End Time.

    =(VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)


    This will give you the hourly difference such as 5.24. Multiplying that by 60 will give you the total number of minutes.

    =((VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)) * 60


    NOTE: This will only cover times that are within the same day. If your dates are not the same for the Start and End Times, then a different approach would be needed.

  • Perfect! Worked wonderfully and easy to remember. What would the approach be for when Start and End times are not within the same day? For my purpose, the time difference would only be a couple hours at most.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You just HAD to go there... Ugh... Haha.


    So you wouldn't have multiple days in between start and finish? If it ran over into day 2, it would end in day 2 and never extend past that? Is it possible that the end time will still be later than the start time even though it rolls over into the next day (0300 today until 0400 tomorrow)?

  • I know, getting past the midnight threshold is extra! The End time on day 2 wouldn't exceed to a day 3. Also the End time wouldn't roll past the initial Start time on the previous day. I'm thinking only 10 hours at the very most between the two times. That should make it easier to figure out. So just 2230 today and 0400 tomorrow?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. Let me think on the most efficient way to accommodate this without having to delve into the more complex mess of things...


    So we have our initial formula...

    =((VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)) * 60


    We need to factor the end time being technically less than the start time, but in a different day, so our end time calculations come from here...

    VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60


    We need to say that IF the End Date is greater then the Start Date

    =IF([End Date]@row > [Start Date]@row, ........................................


    Lets try adding 24 hours and then subtract the start from that...

    =IF([End Date]@row > [Start Date]@row, 24) + VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60


    Now we drop that back into our original formula in place of the original end time conversion to get...

    =((IF([End Date]@row > [Start Date]@row, 24) + VALUE(LEFT([End Time]@row, 2)) + VALUE(RIGHT([End Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)) * 60


    Try running that through a few different scenarios and let me know how it works out for you. This has also given me an idea on a simpler way to account for multiple days... Hmm...

  • Yes, that definitely worked! Such a good looking formula too.

    One catch on your formula is the initial IF statement should be End time < Start time. You had said 'less than' earlier in the explanation though.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You are correct in that if we are referencing the TIME it should be less than. I had just assumed that you also used start and end DATE columns as well.


    In my formula I specified that the end DATE is greater than the start DATE, then proceeded to run the calculations off of the times themselves.


    If you are strictly using time columns, then yes, you would need to flip it so that the end TIME is less than the start TIME.

  • I have this working and it seems to be adding an extra hour in places:

    The time for 01/06/16 should be 12 hours and 27 minutes. Others below it work fine, but some don't....


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Jay Long Exactly what are your formulas? Can you copy/paste them from the sheet to here?

Sign In or Register to comment.