Duration between Military Time

Brittany Hajicek

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

Paul 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.

Brittany Hajicek

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 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)?

Brittany Hajicek

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 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...

Brittany Hajicek

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 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.

Jay Long

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 Newcome

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

Silvia Rangel

@Paul Newcome I have read thru the thread and can't make sense of the formulas (sorry! ☺) I am trying to calculate the total time between start and end of a task. Start and End are being manually entered and just need to calculate the total hours in between. All the formulas l have looked at seem too complicated for what l am trying to achieve and l tried a few without success. Any ideas?

Paul Newcome

@Silvia Rangel

Here is a thread with a number of time based solutions. If you are unable to find one that works for you, feel free to let me know, and I will see if we can help walk you through something.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Chaserlindy

@Paul Newcome I know this is extremely old at this point but I am looking for this exact same formula but with it formatted as 00:00:00 (HH:MM:SS) with seconds included. If you are still around, would you be willing to help or point me in the right direction? Thank you!!

For reference:

Start Time : 16:30:30 | End Time : 16:31:15

Paul Newcome

@Chaserlindy I believe there may be something in here that tackles seconds. It is quite a few pages long, but I am fairly certain there should be something for you. If not, please feel free to comment there so we can try to keep all of the time based solutions in one place.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Deb Lee

@Paul Newcome Sorry to bother, but I'm not quite following - I have a similar issue but what i'm looking for is multiple days, different times that have been booked out and in for car usage.

They want me to calculate - hours, days and weeks for when the cars are used.

If the times have been converted to 24 hour times, what would be the equation then?

And would it be best to be done in a separate sheet and pull the data from the log sheets into a new one or would it be better just to add some more columns to do the analysis?

Thanks!

Paul Newcome

@Deb Lee Please take a look through the thread linked above. There should be something in there that will get your times converted into usable numbers to run the calculations and then convert it back into a more "display friendly" format.