Excel stores time durations as days
Published on in Excel
For example, 6 hours is stored as 0.25 days.
To format a duration as
first convert the duration to days.
Table of contents
A friend of mine consulted me with this problem: he was copy-pasting a bunch of time durations to Excel and wanted to calculate their average duration.
The values were not in Excel-friendly format,
and he didn't want to modify them by hand,
AVERAGE function didn't work out of the box.
Here's an example of a single time duration:
That's one minute, 39 seconds and 811 milliseconds.
I first tried to change the formatting of the data, hoping that Excel would correctly identify the data as time durations. But I couldn't get that to work.
I then tried to parse the text and pass its parts to the
which takes three parameters:
hours, minutes and seconds.
But passing a decimal number (i.e. seconds with milliseconds) as the third parameter
In the end,
I found it easiest to convert the raw data to milliseconds
Here's the formula for the B column, assuming that the original data resides in the A column:
// A1 contains a duration, e.g. "1:39.811" // B1 contains the duration in milliseconds. // In this case it would be "99811" = // Minutes: // the first character from the left. // Doesn't support 10+ minute durations, // but that's probably OK with this data. LEFT(A1; 1) * 1000 * 60 // Seconds: // starting from the third character (1-indexed), // take two characters in the middle. + MID(A1; 3; 2) * 1000 // Milliseconds: // the three rightmost characters. + RIGHT(A1; 3)
Now that the B column contains the time durations in milliseconds, it's easy to calculate the average value:
But how to format the result as a time duration?
Luckily, someone had asked "[How to] convert milliseconds to hh:mm:ss.000" on Reddit four years ago, and someone called sqylogin had shared this nugget of wisdom (formatted for clarity):
Excel stores data in days.
Therefore, 1 millisecond = 1/24/60/60/1,000 = 1/86,400,000.
To convert 586 milliseconds to an Excel "day," divide it by 86.4 million, and in the resulting cell, use this custom number format:
Ah, so calculating the average time duration is as easy as this:
=AVERAGE(B1:B999) / 86400000
Plus the formatting of the cell displaying the average value needs to be set to this:
Egg-cellent! Now my friend can keep copy-pasting values to the A column, and the average value will be updated automatically. He can even hide the B column since it's only used as an intermediate step to calculate the average value.
Excel is often a very powerful and quick tool to use. But sometimes it doesn't work like I would want it to, so I end up fighting it and coming up with hacky solutions like this.
To be fair, maybe that applies to all tools and even programming in general. 😅