Use Excel to calculate the time (time difference, hours worked, add/subtract).
Excel allows you to quickly perform basic arithmetic operations and apply formulae to date and time variables since they are stored as integers in the database.
For instance, you might combine two different time or date values, or you could figure out how much time separates two provided dates or times.
In this lesson, I’ll demonstrate a few techniques for utilizing time in Excel calculations (such as calculating the time difference, adding or subtracting time, showing time in different formats, and doing a sum of time values)
This instruction explains: 1. How Does Date and Time Work in Excel? 2. Calculating the Time Difference Between Two Times using Formulas 2.1 Excel's simple subtraction function for calculating the time difference 2.2 The time difference can be calculated in hours, minutes, or seconds. 2.3 Utilizing the TEXT function to determine the time difference 2.4 Consider the Time Difference in One Unit (Hours/Minutes) and disregard everything else 2.5 Do a time-to-date calculation (from the start time) 3. Time When Date Changes Calculation (calculate and display negative times in Excel) 4. Time Addition and Subtraction in Excel 4.1 TIME Function Utilization 4.2 Using Elementary Arithmetic 5. Summing time in Excel 5.1 How to SUM in Excel over 24 hours 6. Results displaying a hash (###) rather than a date or time (explanations and fix) 6.1 The column is too narrow. 6.2 Date Value Negative
1. How Does Date and Time Work in Excel?
As I previously stated, dates and timings are kept in Excel as integers in a cell. The decimal portion of a number would represent the portion of the day, while a whole number would indicate a whole day (which can be converted into hours, minutes, and seconds values)
For instance, the number 1 in Excel stands for January 1st, 1900, the day from which Excel begins to take dates into account.
Thus, 2 would denote January 2, 1990, 3 would denote January 3, 1900, and so on, and 44197 would denote January 1, 2021.
Note: The starting dates for Excel for Windows and Excel for Mac are different. Excel for Windows would interpret 1 as January 1, 1900, and Excel for Mac would interpret 1 as January 1, 1904.
These numbers can be transformed into hours, minutes, and seconds if there are any digits after the decimal point, which Excel will treat as part of the day.
For instance, 44197.5 would denote 12:00:00 PM on January 1, 2021.
Therefore, dealing with time values in Excel is equivalent to working with a number’s decimal part.
Additionally, Excel provides you the option to format that decimal element in a variety of ways, including hours, minutes, seconds, or a mix of hours, minutes, and seconds.
Now that you know how Excel stores time, let’s look at some instances of how to determine the difference in time between two dates or times in Excel.
2. Calculating the Time Difference Between Two Times using Formulas
Often, all you need to do is calculate the total amount of time that has passed between the two-time numbers (such as in the case of a timesheet that has the In-time and the Out-time).
The technique you use will depend on how the time is expressed in a cell and how you want the output to be formatted.
Here are a few samples to get you started.
2.1 Excel’s simple subtraction function for calculating the time difference
Since time is represented as a number in Excel, you can easily subtract the start time from the end time by finding the difference between the two time values.
End Time – Start Time
A decimal number representing the amount of time that has passed between the two-time values would also be the result of the subtraction.
Here is an illustration of how I used the start time and finish time to get the time difference using a straightforward subtraction.
Your findings could be shown using the time format (instead of decimals or hours/minutes values). In the example we used above, cell C2’s outcome displays 09:30 AM rather than 9.5.
That’s good since Excel is attempting to duplicate the format from the column next to it.
Change the format of the cells to General to convert this to a decimal (the option is in the Home tab in the Numbers group)
Once you’ve obtained the outcome, you can format it in various ways. For instance, you can display the value in hours, minutes, or both hours, minutes, and seconds.
The many formats you can utilize are listed below:
| Format | What it Does |
| h | Shows only the hours elapsed between the two dates shows |
| hh | Shows hours in double-digit (such as 04 or 12) |
| hh:shows | Shows hours and minutes elapsed between the two dates, such as 10:20 |
| hh:mm: ss | Shows hours, minutes, and seconds elapsed between the two dates, such as 10:20:36 |
And if you’re unsure about where or how to use these unique date formats, just follow these instructions:
- The cells you wish to apply the date format to should be selected.
- Press the 1 key while holding down the Control key (or Command + 1 on a Mac).
- Select the Number tab in the Format Cells dialogue box that appears (if not selected already)
- Click Custom in the left pane.
- In the Type area, enter any appropriate format code (in this example, I am using hh:mm:ss)
- Input OK.
The aforementioned actions would alter the formatting and display the value according to the format.
It should be noted that customized number formatting does not affect the cell’s value. It just modifies the way a value is displayed. So, a cell can still display the original value even if I choose to only display the hour value in it.
Use the custom number format shown below if the total number of hours is greater than 24 hours: [hh]:mm:ss
2.2 The time difference can be calculated in hours, minutes, or seconds.
Excel provides a decimal number that indicates the time difference created by summing the time data.
The decimal portion of the number would represent that portion of the day that can be easily converted into hours, minutes, or seconds since each whole number represents one day.
Calculating Hours of Time Difference
Suppose you want to determine the number of hours between the two-time values in the dataset that is depicted below.
The equation that will calculate the time difference in hours is as follows:
=(B2-A2)*24
The total number of hours that passed between the two times may be calculated using the method above.
Excel will occasionally offer you the result in time format to be helpful (as shown below).
By selecting Number as the format and clicking on the Home tab, you can easily convert this into Number format.
Use the following formula if you simply want to retrieve the total number of hours that passed between the two periods (without any decimal parts):
=INT((B2-A2)*24)
Note:
Only the same day’s time values can be used in this formula. This formula will produce inaccurate answers if the day changes (and one of the time values is from a different date while the second is from a different date). Look at the portion of this lesson where I discuss the formula to determine the time difference when the date changes.
Calculating Minutes of Time Difference
The total number of minutes in a day (1440 or 24*60) must be multiplied by the result to determine the time difference in minutes.
Let’s say you want to determine the total number of minutes that passed between the start and finish dates and you have the data set depicted below.
The equation to accomplish it is given below:
=(B2-A2)*24*60
Calculating the Seconds of a Time Difference
You must multiply the result by the total number of seconds in a day (which is equal to 24 * 60 * 60 or 86400) to determine the time difference in seconds.
Let’s say you want to determine the total number of seconds that have passed between the start and finish dates and you have the data set depicted below.
The equation to accomplish it is given below:
=(B2-A2)*24*60*60
2.3 Utilizing the TEXT function to determine the time difference
Using the TEXT function is another simple method for obtaining the time difference fast without having to bother about modifying the format.
You may specify the format directly within the formula by using the TEXT function.
=TEXT(End Date - Start Date, Format)
The calculation you wish to perform is the first parameter, and the format in which you want to present the computation’s outcome is the second.
Consider a scenario in which you have the dataset depicted below and wish to determine the interval of time between the two periods.
Here are a few formulas that will provide you with the outcome in various formats.
Just display the hours:
=TEXT(B2-A2,"hh")
The result of the aforementioned formula will only display the number of hours that passed between the two-time values. If your answer is 9 hours and 30 minutes, you will still see 9.
Display the total amount of minutes.
=TEXT(B2-A2,"[mm]")
Display the total number of seconds.
=TEXT(B2-A2,"[ss]")
Hours and Minutes Display
=TEXT(B2-A2,"[hh]:mm")
Hours, Minutes, and Seconds Display
=TEXT(B2-A2,"hh:mm:ss")
If you’re curious what the difference between hh and [hh] in the format (or mm and [mm]) is, you may find out by using square brackets. This is true even if the hour value is more than 24. Therefore, using [hh] will give you the entire number of hours and hh will only give you the hours that have passed on the day of the end date if you subtract two date values with a difference of more than 24 hours.
2.4 Consider the Time Difference in One Unit (Hours/Minutes) and disregard everything else
Use the specialized HOUR, MINUTE, or SECOND function to get the time difference between two-time values using only the number of hours, minutes, or seconds.
Each of these functions accepts just the time value as an input and returns the appropriate time unit.
Suppose you wish to determine how many hours, minutes, and seconds have passed between these two periods and you have the data set depicted below.
The formulae to achieve this are listed below:
Counting the Number of Hours Between Two Times
=HOUR(B2-A2)
Minutes are calculated using the time value result (excluding the completed hours)
=MINUTE(B2-A2)
Seconds are calculated from the time value result (excluding the completed hours and minutes)
=SECOND(B2-A2)
You should be aware of the following when using these HOURS, MINUTE, and SECOND formulas:
- There can be no negative difference between the start time and the finish time (which is often the case when the date changes). These formulae would yield a #NUM! error in such circumstances.
- Only the time component of the resulting time value is used in these formulas. In this case, the HOUR formula will yield 10, the MINUTE formula will yield 32, and the SECOND formula will yield 44 if the time difference between the start and end times is 2 Days, 10 Hours, 32 Minutes, and 44 Seconds.
2.5 Do a time-to-date calculation (from the start time)
Instead of using the End time formula, you may use the NOW formula to determine how much time has passed between the start time and the present now.
When used in a cell, the NOW function returns the current date and time. It belongs to the class of functions that don’t accept any input arguments.
Therefore, you can use the following formula to determine the total amount of time that has passed between the start time and the current time:
=NOW() - Start Time
The start timings are in column A in the example below, and the amount of time that has passed thus far is in column B.
You can arrange the result to display both the day and the time component if the time difference between the start date and time and the present time is more than 24 hours.
Use the TEXT formula listed below to accomplish that:
=TEXT(NOW()-A2,"dd hh:ss:mm")
The same result may also be obtained by modifying the cell’s custom formatting (which was previously discussed in this article) to include both the day and the time components.
If the sole part of your start time is the time, Excel will treat it as the time on January 1st, 1990.
In this situation, using the NOW function to determine the amount of time that has passed thus far will produce an inaccurate answer (as the resulting value would also have the total days that have elapsed since 1st Jan 1990).
=NOW()- INT(NOW())-A2
The above formula subtracts the day component from the number provided by the now function using the INT function, which is then used to determine the time difference.
The volatile function NOW updates every time the worksheet is changed, but it does not update in real time.
3. Time When Date Changes Calculation (calculate and display negative times in Excel)
The techniques described up to this point are effective if your end time is later than the start time.
But when your finish time is later than your start time, a problem occurs. This frequently occurs while filling out timesheets and merely entering the time and not the whole date and time.
In such circumstances, there is a chance that your finish time might be sooner than your start time if you are working a one-night shift and the date changes.
For instance, if you begin working at 6:00 PM and finish working and take a break at 9:00 AM.
If you are only dealing with time values, deducting start and end times will result in a negative value of nine hours.
Additionally, Excel cannot deal with negative time values (and for that matter nor can humans, unless you can time travel)
In these situations, you need a method to determine that the day has changed and that the computation has to be adjusted.
Fortunately, this has a fairly simple cure.
Consider the following dataset, where I have the start time and the end time.
You may have noticed that the start time might occasionally be in the evening and the finish time in the morning (which indicates that this was an overnight shift and the day has changed).
If I compute the time difference using the formula below, it will display the hash symbols in the cells where the outcome is a negative value (highlighted in yellow in the below image).
=B2-A2
Here is an IF formula that checks to see if the time difference value is negative or not and provides the correct answer if it is.
=IF((B2-A2)<0,1-(A2-B2),(B2-A2))
Even while this generally works well, it still fails when the start time and the finish time are more than 24 hours apart. For instance, if someone enters on day 1 at 9:00 AM and leaves on day 2 at 11:00 AM.
There is no way to determine whether the user signed out after two hours or after 26 hours because this spans more than a day.
The best way to handle this would be to ensure that the entries also include the date, but if you are only working with the time, the formula above should take care of the majority of the problems (considering it is unlikely that anyone would work for more than 24 hours).
4. Time Addition and Subtraction in Excel
In the cases we’ve seen so far, we had the start time and the finish time, but we needed to figure out the time difference.
Additionally, Excel makes it simple to modify the existing date and time values by adding or removing a fixed time value.
Consider the scenario where you have a list of jobs in a queue, each of which is taking the allotted amount of time, and you want to know when each task will finish.
In this scenario, you can simply multiply the time each task will take by its start time to determine when it is anticipated to be finished.
You must make sure that the time you are trying to add adheres to the format that Excel currently respects since Excel saves date and time information as integers.
For instance, Excel will display the following date if you add 1 to the date. This is because, in Excel, 1 equals one day (which is equal to 24 hours).
Therefore, you cannot just add 1 to an existing time value to make it 1 hour longer. Make sure to convert the hour value to the decimal place that corresponds to one hour. and adding minutes and seconds is the same.
4.1 TIME Function Utilization
The Excel time function converts the values of the hour, minute, and second into a decimal number that represents this time.
For instance, I may use the following formula to increase a time by 4 hours:
=Start Time + TIME(4,0,0)
If you know how many hours, minutes, and seconds you want to add to an existing time, you can use the TIME function to do it without having to worry about how to properly convert the time to a decimal number.
Also keep in mind that the TIME function will only take into account the integer portion of the inputted hour, minute, and seconds values. For instance, the TIME function would only add five hours and ignore the decimal component if I entered 5.5 hours.
A further point to be made is that the TIME function may only add numbers that are less than 24 hours. This would result in an incorrect result if your hour value was greater than 24.
The function will only take into account values that are less than 60 minutes and 60 seconds for the minutes and seconds part as well.
You can subtract time using the TIME function, just like I did when I added time. Simply substitute a negative sign for the + sign in the formulas above.
4.2 Using Elementary Arithmetic
Even though using the time function is simple and convenient, there are a few limitations (as covered above).
Use the mathematical approach I’ll discuss here if you’d like greater control.
The idea is straightforward: add the time value to any other time value in Excel by converting it to a decimal number that reflects the fraction of the day.
For instance, you can use the following formula to add 24 hours to an existing time value:
=Start_time + 24/24
Simply said, I’m increasing the existing time value by one day.
The following formula may be used to add 30 hours to a time value, continuing with the same idea:
=Start_time + 30/24
The aforementioned formula accomplishes the same thing, with the decimal component of the number (30/24) representing the hours, minutes, and seconds and the integer part representing the total number of days in the time that you wish to add.
Similar to this, you may use the following formula to add a certain amount of minutes to a time value:
=Start_time + (Minutes to Add)/24*60
And if you know how many seconds you want to add, you may use the calculation below:
=Start_time + (Minutes to Add)/24*60*60
Although using the time function is simpler, I think this approach is much superior because it applies to all circumstances and uses the same basic idea. You don’t have to worry about whether the time you want to add is less than 24 hours or more than 24 hours, unlike the time function.
The same strategy works when time is subtracted as well. Simply substitute a minus sign for the + in the calculations above.
5. Summing time in Excel
In Excel, you might occasionally wish to rapidly sum up all the time data. Excel makes adding multiple time values quite simple (all it takes is a simple SUM formula)
However, there are a few things you should be aware of when you add time in Excel, particularly the cell format that will display the outcome.
Let’s look at an illustration.
I have a list of tasks below, along with the amount of time that each task will require in column B. I want to quickly add these times so that I can determine the total amount of time that will be required for all of these tasks.
I’ve used a straightforward SUM calculation in cell B9 to estimate how long each of these jobs will take in total. The result is 18:30. (which means that it is going to take 18 hours and 20 minutes to complete all these tasks)
So far, so good!
5.1 How to SUM in Excel over 24 hours
Now see what happens if I increase the time required to perform Task 2 from 1 hour to 10 hours.
Now that the time has changed to 03:20, it should take 3 hours and 20 minutes to do all of these chores.
This is false (obviously)
Excel’s errors are not the issue here. The formatting of the cell in this case means that it will only display the time component of the result, which is a problem.
Since the final number, in this case, is greater than 24 hours, Excel chose to transform the excess time into a day, remove it from the user’s view, and just show the remaining hours, minutes, and seconds.
Thankfully, this is simple to correct.
To force the cell to display hours even if the time exceeds 24 hours, all you have to do is change the cell format.
You can use the forms listed below:
| Format | Expected Result |
| [h]:mm | 28:30 |
| [m]:ss | 1710:00 |
| d “D” hh:mm | 1 D 04:30 |
| d “D” hh “Min” ss “Sec” | 1 D 04 Min 00 Secs |
| d “Day” hh “Minute” ss “Seconds” | 1 Day 04 Minute 00 Seconds |
By using the custom format in the format cells dialogue box, or by using the TEXT function and one of the aforementioned formats in the formula itself, you may modify the format.
Even if the duration spans more than 24 hours, you may still display the time using the formula below the TEXT:
=TEXT(SUM(B2:B7),"[h]:mm:ss")
Alternatively use the following formula to change hours that are longer than 24 hours into days:
=TEXT(SUM(B2:B7),"dd hh:mm:ss")
6. Results displaying a hash (###) rather than a date or time (explanations and fix)
In rare circumstances, Excel could display the hash symbols in the cell in place of the time value.
Here are a few potential causes and solutions:
6.1 The column is too narrow.
Hash symbols may appear in cells when there isn’t enough room to display the full date.
It can be quickly fixed by enlarging the columns’ width.
6.2 Date Value Negative
In Excel, a date or time value cannot be negative. Excel will display hash symbols if you calculate the time difference and the result is negative.
The formula can be changed to produce the desired outcome. For instance, you would need to modify the formula if the date changed while you were determining the time difference between two times.
In other circumstances, you may utilize the ABS function to change the negative time value into a positive one so that it can be shown properly. To check if the result is a negative value and return a more meaningful value, you can also use an IF formula.
I discussed how to use Excel to compute time in this lesson (where you can calculate the time difference, add or subtract time, show time in different formats, and sum time values)
