Excel stores time durations as days

For example, 6 hours is stored as 0.25 days. To format a duration as hh:mm:ss.000, first convert the duration to days.

Table of contents

Example problem

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, so Excel's AVERAGE function didn't work out of the box.

Here's an example of a single time duration:

1:39.811

That's one minute, 39 seconds and 811 milliseconds.

Failed attempts

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 TIME function which takes three parameters: hours, minutes and seconds. But passing a decimal number (i.e. seconds with milliseconds) as the third parameter didn't work.

Solution

Converting the raw data to milliseconds

In the end, I found it easiest to convert the raw data to milliseconds using Excel's LEFT, MID and RIGHT functions.

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:

=AVERAGE(B1:B999)

But how to format the result as a time duration?

Displaying milliseconds as a 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: HH:MM:SS.000.

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:

m:ss.000

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.

The actual learning

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.