# 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 `hh:mm:ss.000`, first convert the duration to days.

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