How to Calculate Time in Excel? 2 Distinct Types
(Note: This guide on how to calculate time in Excel is suitable for all Excel versions including Office 365
Microsoft Excel is one of the most influential and useful data management tools. It helps manage, organize, and oversee data from multiple sources with ease.
One simple yet essential use of Excel is that it helps in performing operations relating to time. In Excel, you can subtract or find the difference between a variety of numerical data easily. But, performing operations like adding or subtracting time is a bit different.
In this article, you will learn how to calculate time in Excel. You will learn how to add time, calculate time difference in Excel, and express them as different units.
You’ll Learn:
- How to Add Time in Excel?
- How to Calculate Time Difference in Excel?
- Calculate Time Difference in Different Formats in Excel
Related Reads:
Using Dates and Times with Microsoft Excel 2016
How to use Date and Time Functions in Microsoft Excel 2013
Short Date Format in Excel – 3 Different Methods
How to Add Time in Excel?
You can add time easily in Excel and get the output as a total value. Imagine you are working on a group project and every single person in your group has contributed certain working hours for that particular project. Or, you might want to calculate the total time taken to complete the project by adding up small individual tasks.
Let us see how.
Add Time Less Than 24 Hours
Consider an example where you want to find the total time you have spent on a project. But, you have worked on multiple tasks for certain time intervals. There are four tasks each counting to 3 hours 45 minutes, 5 hours 5 minutes, 3 hours 20 minutes, and 10 hours 10 minutes respectively.
Note: It is always better to change the format of the cells to Time. To change the format of the cells, navigate to Home. Under the Number section, click on the dropdown from General/Custom and select Time.
- If you want to calculate the total time spent on the project, first, choose a destination cell.
- Enter the formula =SUM(range), where range denotes the cells with the time data. Or, you can individually add cells with a plus (+) sign between them.
This gives you the total time taken to complete the task i.e. a total of 22 hours and 20 minutes.
Add Time Greater Than 24 Hours
From the above-mentioned example, it seems that another task has been added which takes a time duration of another 10 hours and 15 minutes.
If we calculate time using the above method, Excel shows you the total time as 8 hours and 35 minutes.
But, this is not the correct total time. We get the value 8:35 because Excel follows the 24-hour format.
- To show the time as more than 24 hours, first, select the cell. In this case, select cell D15.
- Navigate to Home. Under the Cells section, click on the dropdown from Format and select Format Cells. Or, right-click on the cell and select Format Cells.
- Under the Number tab, click on Custom from the Category section.
- In the Type text box, enter [h]:mm:ss;@ and press OK.
This gives you the resultant time in a 24-hour format. That is, the total time taken to complete the task is 32 hours and 35 minutes.
Suggested Reads:
How to Sort Dates in Excel? 6 Easy Methods
How to Merge Excel Files? 5 Proven Ways
How to Save an Excel File? 4 Different Ways
How to Calculate Time Difference in Excel?
Similar to adding two or more time values to get the total time taken, calculating the time difference in Excel is also a bit different compared to standard subtraction.
Imagine you are a part of the resource department in an organization. You have some employees working under you. You want to calculate the total working hours any particular employee has logged in the day.
Calculate Time Difference Within 24 Hours
Before you start to calculate the time difference, make sure the time you have entered is formatted correctly. If not, navigate to Format Cells and select Time. You can either choose the 24-hour format or the 12-hour format. If it’s a 12-hour format, choose the appropriate type and add AM or PM.
- To calculate the time difference, choose a destination cell.
- Enter the formula to subtract the value in the two cells. In this case, enter =C4-B4 and press Enter.
- This will give you the time difference between the given hours.
But, you can see that the time difference bears the AM or PM notification.
- To remove them, right-click on the cells and select Format Cells.
- In the Number section and click on Time. In the Type section, select the hh:mm format and click OK.
This gives you the time difference between the time intervals.
Calculate Time Difference Exceeding 24 Hours
Sometimes, you will have to find the time elapsed for more than a period of 24 hours. In such cases, using the above method might not give you absolute results.
- First, enter the data and time data in the respective cells. To change the format of the data, right-click on the respective cells and select Format Cells.
- In the Format Cells dialog box, select the Custom category. In the Type textbox, enter m-d-yyyy h:mm AM/PM.
- Click OK.
Thus, we have the time required to calculate the difference.
Now, subtract the cells containing the time values. In this case, enter the formula =(B12-B11)*24 in the destination cell. Here, multiplying the resultant value by 24 gives you the proper time difference in hours.
Calculate Time Difference in Different Formats in Excel
When you subtract 2 time values to calculate the difference, Excel just returns the time difference value based on the set/default format. In some cases, you might want Excel to return the time value in a different format.
On one hand, you can change the format of the time difference by using the Cells dialog box. On the other hand, there are a couple of methods to help you express the time difference as distinct units.
Let us see how.
Using the Text Function
Using the TEXT function, you can choose the format of the difference in time values. Let us get the desired outputs using the above example.
- To calculate the time difference only in houjik furs, enter the formula =TEXT(B18-B17,”h”) in the destination cell and press Enter. This gives you the time difference only in the form of hours.
- To calculate the time difference in hours and minutes, enter the formula =TEXT(B18-B17,”h:mm”) in the destination cell. Once you press Enter, Excel returns the output as hours and minutes.
- If you want to display the output in the form of hours, minutes, and seconds, enter the formula =TEXT(B18-B17,”h:mm:ss”) in the destination cell.
Note: The TEXT function returns the desired value either in hours, hours and minutes, or hours, minutes, and seconds format only if the time difference is within the same day.
Multiplying by a Number
If you are particularly looking to find the time difference as a whole number either as hours, minutes, or seconds, you can multiply the resultant time difference by a particular number.
- To find the total hours between the two times, find the difference between the start time and end time and multiply it by 24.
- If you only want to find the minute difference between the time intervals, find the time difference and multiply the resultant value by 1440.
- To find the total seconds between the time intervals, calculate the difference and multiply the result by 86400.
The multiplied values signify the total number of hours(24), minutes(1440), and seconds(86400) in a day.
Note: This method can be used to calculate the time differences between intervals of more than 24 hours.
Also Read:
How to Insert Radio Buttons in Excel? A Step-by-Step Guide
How to Remove Dropdown in Excel? 3 Easy Methods
How to Remove Dotted Lines in Excel? 3 Different Cases
Frequently Asked Questions
Why am I getting the ##### error when I find the time difference?
To get the time difference, we have to subtract the start time from the end time. If you subtract the end time from the start time, Excel displays the #### error.
How to get the time difference as negative values?
To get the time difference as a negative value, navigate to File and click on Options. In the Excel Options dialog box, select Advance. Scroll down and check the checkbox for Use 1904 date system.
How do I find the time difference in Excel for more than 24 hours?
To find the time difference, first, make sure the time values are properly formatted in the format of m-d-yyyy h:mm AM/PM. Then, subtract the end time from the start time and then multiply the difference by a specific number (24,1440, or 86400) to get the difference.
Closing Thoughts
Calculating and formatting time has always been a crucial application of Excel. It is used in a variety of places from academic to corporate purposes, from an individual level to project and management level.
In this article, we saw how to add time in Excel when the total time is within 24 hours and when it exceeds 24 hours. We also saw how to calculate the time difference in Excel when the start and end times are within 24 hours and when they exceed 24 hours. Additionally, we learned how to calculate the time difference in different formats such as hours, minutes, and seconds or altogether.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
