Time Difference in Excel stands as a reliable ally in the realm of precise time management and data analysis. This versatile feature is your key to unlocking scheduling efficiencies, tracking performance metrics, and maintaining accurate records. ‘Time Difference in Excel’ empowers you to harness the true potential of your time-related data, transforming raw information into actionable insights. As you navigate the complex landscape of time-related calculations, this indispensable tool guides you with precision and clarity, ensuring that your scheduling, analysis, and reporting endeavors are always on the mark. Embrace the power of ‘Time Difference in Excel’ to streamline your time-related tasks, making each calculation a breeze and each decision an informed one.
In this tutorial, I’ll demonstrate a few techniques for using 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 Tutorial Covers:
- Date and time handling in Excel
- Time difference between two times calculation formulas
- Excel’s simple subtraction function for calculating time difference
- The time difference can be calculated in hours, minutes, or seconds
- Calculating Time Difference in Hours
- Calculating Time Difference in Minutes
- Calculating Time Difference in Seconds
- Utilizing the TEXT function to determine time difference
- Consider the Time Difference in One Unit (Hours/Minutes) and disregard everything else
- Determine elapsed time Till Now (from the start time)
- Time When Date Changes Calculation (calculate and display negative times in Excel)
- Time Addition and Subtraction in Excel
- TIME Function Utilization
- Using Basic Arithmetic
- In Excel, how to SUM time
- Using Excel, SUM over a 24-hour period
- Results displaying a hash (###) rather than a date or time (explanations and fix)
- The column’s width is insufficient
- Date Value Negative
1. Date and time handling in Excel:
As previously stated, dates and times are kept in Excel as numbers in a cell. The decimal portion of a number would represent the portion of the day, and 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 forth, and 44924
would denote December 29, 2022.
The commencement dates for Excel for Windows and Excel for Mac are different. Excel for Windows would interpret 1 as January 1, 1900, whereas 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, 44924.5 would denote 12:00:00 PM on December 29, 2022.
Therefore, dealing with time values in Excel is equivalent to working with a number’s decimal part.
Additionally, Excel gives you the option to format that decimal element in a variety of ways, including hours, minutes, seconds, or a combination of hours, minutes, and seconds.
Now that you know how Excel stores time, let’s look at some instances of how to calculate the difference between two dates or times in Excel.
2. Time difference between two times in Excel calculation 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 result to be formatted.
Here are a few examples to calculate time in excel.
Calculate time in Excel through a simple subtract function
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 value 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 end time to determine the time difference using a straightforward subtraction.
Your findings might be displayed 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 fine because Excel is attempting to duplicate the format from the column next to it.
Change the format of the cells to “General” in order 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 many ways. For instance, you can display the value in hours, minutes, or both hours, minutes, and seconds.
The various formats you can utilize are listed below:
| Format | What it Does |
| h | Only displays the hours that have passed between the two dates |
| hh | Demonstrates double-digit hours (such as 04 or 12) |
| hh:mm | Displays the time difference in hours and minutes, such as 10:20. |
| hh:mm:ss | Displays the time difference between the two dates in hours, minutes, and seconds, like 10:20:36. |
And if you’re unsure about where or how to use these unique date formats, just follow these instructions:
Step 1: Choose the cells where the date format should be applied.
Step 2: Press the 1 key while holding down the Control key (or Command + 1 on a Mac). Click the “Number” tab in the “Format Cells” dialog 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). Select OK.
The aforementioned actions would alter the formatting and display the value according to the format.
It should be noted that customized number formatting has no effect on the cell’s value. It just modifies the way a value is shown. So, a cell can still display the original value even if I select to only display the hour value in it.
Pro Tip: Use the custom number format shown below if the total number of hours is greater than 24 hours: [hh]:mm:ss
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 using the time formula.
The decimal portion of the number would indicate that portion of the day that can be easily transformed into hours, minutes, or seconds since each whole number represents one day.
Calculate time difference in Excel – in Hours
Let’s say you have the dataset depicted below and want to figure out how many hours there are between the two times.
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 can be calculated using the formula above.
Excel will occasionally offer you the result in time format in an effort to be helpful (as shown below).
By clicking on the “Home” tab and choosing “Number” as the format under the “Number” group, you can quickly convert this to “Number” format.
Use the following formula if you only want to retrieve the total number of hours in Excel that passed between the two periods (without any decimal parts):
=INT((B2-A2)*24)
Recall that this formula only functions when both time values correspond to the same day. 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 section of this tutorial where I discuss the formula to determine the time difference when the date changes.
- Calculating Time Difference in Minutes:
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 end times and you have the data set depicted below.
The formula to accomplish that is given below:
=(B2-A2)*24*60
- Calculating Time Difference in Seconds:
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 end dates times and you have the data set depicted below.
The formula to accomplish that is given below:
=(B2-A2)*24*60*60
- Utilizing the TEXT function to determine 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 can 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 argument, 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 times.
These formulas will provide you with the outcome in a variety of formats.
Time calculations – just display the hours:
=TEXT(B2-A2,”hh”)
The outcome of the aforementioned algorithm will only display the number of hours that passed between the two-time values. In the event that 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]”)
Display the Hours and Minutes in time format
=TEXT(B2-A2,”[hh]:mm”)
Indicate the hours, minutes, and seconds:
=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 greater than 24. Therefore, using [hh] will give you the total 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.
- 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 only the time value as an argument and returns the appropriate time unit.
Suppose you wish to determine how many hours, minutes, and seconds have passed between these two times and you have the data set depicted below.
The formulas to do this are listed below.
Counting the Hours Between Two Times:
=HOUR(B2-A2)
Using the time value result to calculate minutes (but excluding completed hours):
=MINUTE(B2-A2)
After subtracting the completed hours and minutes from the time value result, you can calculate seconds:
=SECOND(B2-A2)
You should be aware of the following while 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 formulas would return a #NUM in such circumstances. error
- Only the time component of the resulting time value is used in these formulae (and ignore the day portion). In this case, the HOUR formula will yield 10, the MINUTE formula will provide 32, and the SECOND formula will yield 44 if the time difference between the start and finish times is 2 Days, 10 Hours, 32 Minutes, and 44 Seconds.
Determine elapsed time Till Now (from the start time):
Instead of using the End time formula, you can 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 get the total amount of time that has passed between the start time and the present time:
=NOW() – Start Time
The start times are in column A in the example below, and the amount of time that has passed thus far is in column B.
You can format 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 greater than 24 hours.
Use the TEXT formula listed below to accomplish that:
=TEXT(NOW()-A2,”dd hh:ss:mm”)
The same result can 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 component.
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, the NOW function will produce the incorrect answer when used to determine the amount of time that has passed thus far (as the resulting value would also have the total days that have elapsed since 1st Jan 1990).
You can apply the following formula in this situation:
=NOW()- INT(NOW())-A2
The above formula subtracts the day component from the value 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 full date and time.
In such circumstances, there is a chance that your end time would be earlier 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 in the evening and finish working and taking a break at 9:00 AM in the morning.
If you are only dealing with time values, deducting start and end times will result in a negative value of nine hours (9 – 18).
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 calculation needs to be adjusted.
Fortunately, this has a fairly simple cure.
Consider the following dataset, where I know the start time and the end time.
You may have noticed that the start time can occasionally be in the evening and the end 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, if so, provides the correct answer.
=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. As an illustration, a person might sign in at 8:00 AM on Day 1 and out at 12:00 AM on Day 2.
There is no way to determine whether the user signed out after 4 hours or after 28 hours because this spans more than 24 hours.
4. Adding and Subtracting time 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 tasks 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 already respects since Excel stores date and time information as numbers.
For instance, Excel will display the following date if you add 1 to a date. This is due to the fact that in Excel, 1 equal 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.
- TIME Function Utilization:
The Excel time function turns the values of the hour, minute, and second into a decimal integer that reflects this time.
For instance, I may use the following formula to increase a time by 2 hours:
=Start Time + TIME(2,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 value.
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 can only add numbers that are less than 24 hours. This would result in an inaccurate 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 component 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 calculations above.
- Using Basic Arithmetic:
Even though using the time function is simple and convenient, there are a few limitations (as covered above).
Use the arithmetic 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 represents 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 can 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 can use the following formula to add a certain number 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 can 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 better 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. In Excel, how to SUM time:
In Excel, you might occasionally want to quickly add 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 work will require in column B. I want to rapidly add these times so that I can get the overall amount of time that will be required for all of these tasks.
How to sum time is shown below:
Procedure of calculating time in excel
Step 1: In cell B8, type a simple sum formula to calculate the total time all these activities are going to take, and it gives me the value as 16:45:00 (which means that it is going to take 16 hours and 45 minutes to complete all these activities).
- Using Excel, SUM over a 24-hour period:
Check out what happens when I increase Activity 5’s completion time from 1 hour to 10 hours.
Now that the time has changed to 01:45, it should take 1 hour and 45 minutes to finish all of these tasks.
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 an issue.
Since the final value 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 compel the cell to display hours even if the time exceeds 24 hours, all you have to do is modify the cell format.
You can use the forms listed below:
| Format | Expected Result |
| [h]:mm | 28:30:00 |
| [m]:ss | 1710:00:00 |
| d “D” hh:mm | 1 D 04:30 |
| d “D” hh “Min” ss “Sec” | 1 D 04 Min 00 Sec |
| d “Day” hh “Minute” ss “Seconds” | 1 Day 04 Minute 00 Seconds |
By using the custom format in the format cells dialog box, or by using the TEXT function and one of the aforementioned formats in the formula itself, you can change the format.
Even if the duration spans more than 24 hours, you can 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 some circumstances, Excel might display the hash symbols in the cell in place of the time value.
Here are a few potential causes and solutions.
- The column’s width is insufficient:
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.
- 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 verify if the result is a negative value and return a more relevant value, you may alternatively use an IF formula.
I discussed how to use Excel to compute time in this tutorial (where you can calculate the time difference, add or subtract time, show time in different formats, and sum time values).
Application of Time Difference in Excel
- Task Duration Calculation:
- Determine the duration of tasks or events by subtracting the start time from the end time, aiding in project management and scheduling.
- Employee Attendance Tracking:
- Calculate working hours or attendance by finding the time difference between clock-in and clock-out times for employees, simplifying payroll processing.
- Flight and Travel Planning:
- Calculate flight durations or travel times by finding the time difference between departure and arrival times, assisting in itinerary planning.
- Event Timing and Scheduling:
- Determine event timings or session durations by calculating time differences, ensuring efficient event planning and scheduling.
- Age Calculation:
- Calculate ages based on birthdates by finding the time difference between birthdates and the current date, useful for demographic analysis.
- Performance Metrics:
- Analyze response times, service durations, or processing times in various industries, such as customer service or manufacturing, to improve efficiency and performance.
Time difference calculations in Excel facilitate accurate scheduling, tracking, and analysis, making them invaluable for a wide range of applications across industries.
You may be interested:
