Very often, confusion prevails when it comes to calculations based on Time in Excel. Any calculation gives out seemingly random numbers and you don’t know what to do with it. Read on to really understand how Time works in Excel.
Types of Time
There are 2 main ways to interpret time. First one is Time as it shows on the clock – 1 PM, Noon, 5 PM, etc. This is usually the answer to questions like ‘What time is it now?’ or ‘When is the dinner to be served today?’
The second kind of interpretation is time spent – 1 hour, 3 days, a month, etc. This is usually the answer to questions like ‘How long is the movie?’ and ‘How long do I bake it for?’. A more appropriate word for this is duration.
We will look at how to use both these interpretations in Excel.
First, on a Date
All dates in Excel are stored as serial numbers. To see this, put in today’s date into a cell and then change its number format to General (in Home Tab, Number format group). You will see the date changes to a serial number. This serial number starts from 1-Jan-1900 – which has the serial number of 1. 2-Jan-1900 will have a serial number of 2; and it continues like this. The serial number for today’s date is 43200. This simply means that it has been 43,200 days since 1-Jan-1900. If you type 43200 in Excel and press Ctrl + Shift + 3; you will see the date this article was first written.
Because of this, you can do mathematical calculations on dates. You can try adding or subtracting days from some date easily. A similar logic is used for Time.
How Time works in Excel
If you understand how it is stored in Excel, you will have no more problems with it! Let me explain.
Time is stored as a decimal value.
To understand this better, type in 6:00 AM in a cell and change the number format of the cell to general. You will see the value stored by Excel is 0.25.
This is because in a single 24-hour day; 6:00 AM would be one quarter of a day. As we already saw, one day is stored as 1 in Excel. Therefore, a quarter of 1 is 0.25! This proves to you that Time is stored in as a value between 0 and 1. Going by this logic, 12 noon is 0.5 – type it in a cell and change the number format to Time.
This is where the confusion usually happens – 9:00 AM is 0.375 and 3:30 PM is 0.645833333. In other words, time is a fraction of 1. To see the decimal value of an hour, divide 1 by 24.
How Duration works in Excel
Here is some good news – there is no separate system for duration. The decimal values stored in Excel remain the same. All that differs is the number format. This means that to Excel 6 AM and 6 hours is the same. This is because if you try and write 6 AM into a cell, what it means is 6 hours since midnight. In decimals, duration will still stay 0.25 – a quarter of a day. If you say 30 hours, it means one full day plus 6 hours. This is exactly how Excel sees duration too – as 1.25.
If you type out 6:00 in a cell and go to custom number format, the number format will be set to hh:mm. But try typing out 30 hours and check the number format – it will be set to [h]:mm:ss.
As far as duration is concerned, only these two need to be understood. The thing is that there are only 24 hours in a day. Now, the hh:mm format follows clock time limitations. So, it cannot show any duration more than 23 hours and 59 minutes. The 24th hour is midnight – which is stored as zero by Excel.
Therefore, to show any duration that is more than 23 hours and 59 minutes, the number format [h]:mm:ss is used. The square brackets tell Excel that this number is not to be displayed as clock duration, but as duration – in general. Look carefully at the date in the formula bar:
Negative Time Duration
You cannot have a negative unit of time like -6:00 AM, but duration can be negative or positive. For example, in a three-hour exam, you took only 2.5 hours to finish all your answers. This can also be expressed as -0.5 hours, if you are looking at how soon you finished (time taken minus the total time). But try doing this in Excel with a duration format applied. The cell will just show ###
– which is Excel’s way of indicating a display error. There are 2 ways to fix this – either by changing the date settings for the workbook [A] or by using a function [B].
A] On the second Date
There is a second date system that you can switch to – the 1904 date system. It has the following 2 features:
- The difference between the two date systems is in the start of the serial number. In this system, serial number 0 is 1-Jan-1904. Serial number 1 is 2-Jan-1904; and so on.
- This system supports negative durations.
To switch to this system, go to Excel options > Advanced > When Calculating this workbook > Use 1904 date system. The only problem with this is that all the dates in the workbook change by 4 years. So, you need to do this beforehand. To remedy this, we can use a function.
B] Using a function for negative time durations
The trick is to fool Excel into calculating without the negative sign. This is easily done by using the ABS function. Its function is to ignore the signs attached to a number. If we plug our calculation right into this, it works well enough. The display error is resolved.
But what if you want to see the negative sign before this duration? The solution would be to use something like this:
Caution: This will be now become a ‘text’ cell. Which means you cannot do calculations directly on this.
An Example
This is sample data from a race with player names, start & finish times and duration. The duration is simply end time less start time; with a duration number format applied.
Before calculating the leads, the data must be sorted from the shortest to longest duration. The lead for the first one will be zero. The second one will need a formula that calculates how many seconds behind the first, and so on.
As you can see, the negative durations are not displayed. To fix this, use:
="-"&TEXT(ABS(D5-D4),"m:ss")
Here, we do not need hours at all only minutes, if any and seconds. Therefore, the number format is changed to just include minutes. Here, it does not matter if we put square brackets around the [m] as there is no possibility of having more than 59 minutes of lead. Unless this was for the Dakar Rally!
Easy right? Here is a question – what do you think the formula should look like to get the lead from the first one? Post your answers in the comments below!
The Time function in Excel
To make life easier, there is a function just for time. It just requires 3 components to return a time:
- Hour
- Minute
- Second
Using this function makes it a little difficult to enter in time; but it allows for easier calculations. For example, if you need to add a couple of hours to 9:32:28 AM; the function would look like: (see screenshot above)
=TIME(D4+2,E4,F4)
Here, only for the hour component, we are adding 2. Likewise, it becomes very easy to add / subtract minutes or seconds too.
Functions related to the components of time
The 3 components of time are also independent functions. All 3 require only a time value. They will then return only the hours, minutes or seconds as a whole number in a 24-hour format. Because it’s a whole number, duration number formats cannot be applied directly on them. For example, if you try to use the duration number formatting on 14 hours – it will give out a value of 336:00:00. So, the most reliable way to calculate durations would be as we did earlier in this post.
How to use the Time functions
Let’s take the example of a simple production cycle. Product X is made in 5 stages, each taking different amounts of time to manufacture / process. If the production starts at 10:00 AM, the end times for each stage are to be calculated.
Here, we are using the hour and minute functions to first extract only hour and only minutes from the start time. Their respective durations are then added to them individually. Both these calculations are used within the Time function. Therefore, we directly see the calculated end time. Note that seconds are not counted and are set to zero.
This is pretty much everything you will ever need to tackle time in Excel.
Keep Excel-ing!