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.
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)!
Tumbling Wave Software 