Excel Tip of the Week #435 - Working with times

While we haven’t covered this exact topic before, it’s an extension of working with dates, which was discussed last in TOTW #238.

How does Excel store and present time data?

Excel represents time as a number. Whole numbers are used for dates, counting from the non-existent 0th January 1900 as 0 and forward from there. For example today, the 1st March 2022, is day 44,621. Times are then sub-integer divisions – starting with .0 for midnight, so .5 would represent midday, .75 is 6pm / 18:00, and so on. If you don’t include both a date and a time, but just a time, Excel stores these as

All this is not immediately apparent if you enter a time into an Excel cell, or come across one. Then it’s more likely to look like this:

Even in the formula bar, this cell looks like it has a value of 10:00:00. But when you enter something in this format (or just 10:00, or 10 am), then Excel will automatically parse this as a time value. Change the format to number and you can see what’s lurking beneath the surface:

And sure enough this is the value of 10/24.

It’s very important to understand that Excel makes no inherent distinction between a time of day – such as 10 AM – and a duration of ten hours. While we use them differently, to Excel they are stored exactly the same way – as a single value of 10/24.

How can you work with time values in Excel?

The first thing to note is that you can add and subtract dates and times like any other numbers. For example, if you have a list of event durations, you can add them up using a SUM:

However, do be careful if your total goes over a day. The standard Excel time formats – in Excel formatting code they look like hh:mm or hh:mm:ss – only show 24 hour times. If you have a duration totalling 26 hours, it will look like just 2:

To fix this, use the format [hh]:mm – this will display the total amount of hours, rather than wrapping around to the next “day”.

You can also add time to a fixed starting point by using addition – and division to calculate the correct value for the number of hours or minutes later desired:

Once again it’s important to bear in mind the above point about durations exceeding 24 hours – the correct approach is different depending on whether you’re asking “how much time will pass if this duration is 45 minutes longer than expected”, or “what time of day will it be 45 minutes after this start time”. Depending on the context you should amend the formatting appropriately.

You can also subtract times to calculate how long there is between them:

Note that we don’t have to do this conditionally – because the standard time format only shows the result mod 1, the extra day makes no difference for the first three rows. And we don’t have to worry about durations over 24 hours here because we only have times; the result of our subtraction can never be more than 1.

There are also a few time-related functions: HOUR, MINUTE, and SECOND return the appropriate part of a time value, and TIME can be used to construct an Excel-format time from the appropriate parts. There’s also the NOW function which returns the current time and date – if you only want the current time, then =NOW()-TODAY() will do that. Finally, the function TIMEVALUE will attempt to parse any text that represents a time into the appropriate Excel time value.

The key thing to remember with Excel times is to think about if your answer could be negative or more than 1, and if so to plan your formulas and formats appropriately to handle that.

• Previous post - Revisiting making sets of PivotTables
• TOTW index
• Next time – Trimming excess spaces redux!