Excel Date Time Duration

This article explains how to calculate date/time differences between cells, including milliseconds.

I had once received a large amount of log data in an Excel file, and was charged with having to find the duration between some of those logged events. There were thousands of events, time-stamped with values that even included milliseconds! As an example, one of the time stamps looked like this:

Jun/30/2020 09:10:50.500

This indicates a time value which includes 500 milliseconds (or, in other words, a half-second). First of all, it is interesting to note that to display milliseconds in a cell, you have to format the cell with a “.000” at the end of the format. Sticking with the above example, the cell format would be:

mmm/dd/yyyy hh:nn:ss.000

Now, just because we can display milliseconds, doesn’t mean milliseconds is fully supported. In my case, I needed to find the difference between two time-stamps to the nearest millisecond. I was out of luck. None of the Excel formulas I checked supported time calculations involving milliseconds.

The other problem I had was just a quick way to calculate date/time summary statistics over selected ranges of cells. Say, if I selected an entire column of time stamps, I wanted to immediately see the max, min and duration of what I have selected. It seemed like something too tedious to program.

Photo of calendar and clock.

Luckily, there is a little tool you can install that will solve all of these date/time issues. For me, it was these two issues:

  • Time calculations including milliseconds.
  • Date/time summary statistics like earliest, latest and duration of time for whatever cells I selected.

The name of this little tool is:

Excel Date Time Duration

from Tumbling Wave Software. With this little tool, you get a new Excel formula. The name of the formula is:

DATETIMEDURATION

Using the DATETIMEDURATION formula, I was able to calculate the time difference between two cells, even if they involve milliseconds! It is all the date/time functions I ever needed, rolled into one formula.

In addition, the Excel Date Time Duration tool transforms the standard Excel status bar (along the bottom) into a display of date/time statistics. A great feature for me, because the summary changed every time I changed my cell selection, without having to use formulas. The summary includes time duration at all levels: years, months,…, milliseconds. Amazing!

As I step back, I realize there’s a lot of info in this post. I guess the one takeaway is that the Excel Date Time Duration tool will solve a lot of date/time issues, so it’s worth a try if you want to download it from the Tumbling Wave Software website. Have a pleasant evening (or “Have a pleasant millisecond”, as the case may be)!

Embed from Getty Images

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s