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

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