If you’ve been relying on Excel to manage your projects and need help with time calculations, you’re not alone. Figuring out the elapsed time of a project or task is crucial for team leads that need to know what work should be prioritized and how to properly allocate resources.
Simply put, time values are tricky in Excel—especially when calculating time difference, hours worked, or the difference between two employees.
Luckily for you, we’ve done all the hard work to help format cells and calculate elapsed time in Excel faster. Let’s get confident about using Excel for project time tracking and discover the various ways of figuring time.
How to Calculate Time in Excel (A Complete Tutorial and Helpful Formulas)
Summarize this article for me pleaseTypes of Time Calculations in Excel
There isn’t a single formula or format for calculating time in Excel. It depends on your dataset and the goal you want to achieve. Most use a custom format (number over text function) to understand time and date values, or the time difference value.
But there are two types of calculations to get you started, which include:
- Add time: If you need to obtain the sum of two time values to get a total
- Add up to less than 24 hours: When you need to know how many hours and minutes it’ll take to complete two simple tasks
- Add up to more than 24 hours: When you need to add the estimated or actual elapsed time of multiple slow, complex tasks
- Subtract time: If you need to get the total time between a start and end times
- Subtract times whose difference is less than 24 hours: When you need to figure out the hours elapsed since a team member started to execute a simple task
- Subtract times whose difference is more than 24 hours: When you need to know the hours that have gone by since your project started
Summarize this article for me pleaseHow to Calculate Time & Hours in Excel
Let’s go through a few formulas for time calculations in Excel so you get down to the exact hours, minutes, and seconds in your custom time format.
Time Difference in Excel
Before we teach you how to calculate time in Excel, you must understand what time values are in the first place. Time values are the decimal number to which Excel applied a time format to make them look like times (i.e., the hours, minutes, and seconds).
Because Excel times are numbers, you can add and subtract them. And the difference between a start time and an end time is called “time difference” or “elapsed time.”
These are the steps to subtract times whose difference is less than 24 hours:
1. Enter the start date and time in cell A2 and hit Enter. Don’t forget to write “AM” or “PM”
2. Enter the end time in cell B2 and hit Enter
3. Enter the formula =B2-A2 in cell C2 and hit Enter.
WARNING
As you can tell from the figure, this formula doesn’t work for times that belong to different days. This example shows the time difference between two time values within a 24-hour time period.
4. Right-click on C2 and select Format Cells.
5. Choose the Custom category and type “h:mm”
SIDE NOTE
You might wish to obtain the time difference expressed in hours or hours, minutes, and seconds. If so, type “h” or “h:mm:ss,” respectively. This will help you avoid negative time values.
6. Click OK to see the total change to a format describing only the number of hours and minutes in the elapsed time between A2 and B2.
Check out our detailed blog to find a list of time management strategies you can use right away!
Date and time in Excel
In the previous section, you learned how to calculate hours in Excel. But the formula you learned only applied to time differences of less than 24 hours.
If you need to subtract times whose difference is more than 24 hours, you need to work with dates besides times. Do it like this (format cells dialog box):
1. Enter the start time in cell A2 and hit Enter.
2. Enter the end time in cell B2 and hit Enter.
3. Right-click on A2 and select Format Cells.
4. Choose the Custom category and type “m/d/yyyy h:mm AM/PM.”
5. Click OK to see A2 change to a format starting with “1/0/1900” and adjust the date.
6. Use the Format Painter to copy the formatting of A2 to B2 and adjust the date.
7. Enter the formula =(B2-A2)*24 in cell C2 and hit Enter to see the total change to a format describing the number of hours in the time elapsed between A2 and B2.
WARNING
You must apply the “Number” format to C2 to get the correct value in the above formula.
Sum time in Excel
Suppose you want to sum up the time difference between your team members working on multiple project tasks. If those durations add up to less than 24 hours, follow these steps:
1. Enter one duration in cell B2 (with the format h:mm) and hit Enter.
2. Enter the other duration in cell B3 and hit Enter.
3. Enter the formula =B2+B3 in cell B4 and hit Enter.
PRO TIP
If you prefer clicking a single button instead of entering a formula, you may position the cursor in B4 and hit the Σ (or AutoSum) button in the Home tab. The button will apply the formula =SUM(B2:B3) to the cell in the above example.
If the total duration of your project tasks adds up to more than 24 hours, follow these steps instead:
1. Enter one duration in cell B2 (with the format h:mm) and hit Enter. (Warning: Beware that the maximum duration is 23 hours and 59 minutes)
2. Enter the other duration in cell B3 and hit Enter.
3. Enter the formula =B2+B3 in cell B4 and hit Enter.
4. Right-click on B4 and select Format Cells.
5. Choose the Custom category and type “[h]:mm;@.”
6. Click OK to see B4 correctly displaying the sum of the times in B2 and B3.
If you’d like to know more about Excel project management, our blog is the perfect place to start!
More Excel Resources:
- Create a timeline in Excel
- Create a dashboard in Excel
- Create a calendar in Excel
- Create an org chart in Excel
- Create a schedule in Excel
- Capacity planning in Excel
Summarize this article for me pleaseThe Complete List of Formulas for Calculating Time in Excel
It pays to know the formulas when you’re calculating time difference in Excel. To get your time difference and value set up correctly, we’ve provided a list of formulas to make your calculations easier.
| Formula | Description |
| =B2-A2 | Difference between the two time values in cells A2 and B2 |
| =(B2-A2)*24 | Hours between the value in cells A2 and B2 (24 being the number of hours in a day) Warning: If the difference is 24+ hours, you must apply the “Custom” format with the type “m/d/yyyy h:mm AM/PM” to A2 and B2. You also must apply the “Number” format to the cell containing the time difference formula. |
| =TIMEVALUE(“8:02 PM”)-TIMEVALUE(“9:15 AM”) | Warning: The difference must be less than 24 hours. |
| =TEXT(B2-A2,”h”) | Hours between the values in cells A2 and B2 Warning: You must apply the “Custom” format with the type “h” to A2 and B2. Also, the value in B2 cannot be less than A2 and the difference must be less than 24 hours to avoid a negative value. And the TEXT function returns a textual value. |
| =TEXT(B2-A2,”h:mm”) | Minutes and hours between the time values in cells A2 and B2. Warning: You must apply the “Custom” format with the type “h:mm” to A2 and B2. Also, the value in B2 cannot be less than A2 and the difference must be less than 24 hours. |
| =TEXT(B2-A2,”h:mm:ss”) | Hours, minutes, and seconds between the time values in cells A2 and B2. Warning: You must apply the “Custom” format with the type “h:mm:ss” to A2 and B2. And the value in B2 cannot be less than A2 and the difference must be less than 24 hours. |
| =INT((B2-A2)*24) | Number of complete hours between the time values in cells A2 and B2 (24 being hours in a day). Warning: If the difference is 24+ hours, you must apply the “Custom” format with the type “m/d/yyyy h:mm AM/PM” to A2 and B2. And you must apply the “Number” format to the cell containing the time difference formula. |
| =(B2-A2)*1440 | Minutes between the time values in cells A2 and B2 (1440 being minutes in a day). Warning: If the difference is 24+ hours, you must apply the “Custom” format with the type “m/d/yyyy h:mm AM/PM” to A2 and B2. And you must apply the “Number” format to the cell containing the time difference formula. |
| =(B2-A2)*86400 | Seconds between the time values in cells A2 and B2 (86400 being the seconds in a day). Warning: If the difference is 24+ hours, you must apply the “Custom” format with the type “m/d/yyyy h:mm AM/PM” to A2 and B2. And you must apply the “Number” format to the cell containing the time difference formula. |
| =HOUR(B2-A2) | Hours between the time values in cells A2 and B2. Warning: The value in B2 cannot be less than the value in A2 and the difference must be less than 24 hours. Also, the HOUR function returns a numeric value. |
| =MINUTE(B2-A2) | Minutes between the time values in cells A2 and B2. Warning: The value in B2 cannot be less than the value in A2 and the difference must be less than 60 minutes. Also, the MINUTE function returns a numeric value. |
| =SECOND(B2-A2) | Seconds between the time values in cells A2 and B2. Warning: The value in B2 cannot be less than the value in A2 and the difference must be less than 60 seconds. Also, the SECOND function returns a numeric value. |
| =NOW()-A2 | Time elapsed between the date and time in cell A2 and the current date and time. Warning: If the elapsed time is 24+ hours, you must apply the “Custom” format with the type “d “days” h:mm:ss” to the cell containing the time difference formula. Also, Excel doesn’t update the elapsed time in real-time. To do that, you must hit Shift+F9. |
| =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))-A2 | Time elapsed between the value in cell A2 and the current date and time. Warning: Excel doesn’t update the elapsed time in real-time. To do that, you must hit Shift+F9. |
| =INT(B2-A2)&” days, “&HOUR(B2-A2)&” hours, “&MINUTE(B2-A2)&” minutes, and “&SECOND(B2-A2)&” seconds” | Time elapsed between the date and time or time values in cells A2 and B2, expressed in the format “dd days, hh hours, mm minutes, and ss seconds.” Warning: This formula returns a textual value. If you need the result to be a value, use the formula =B2-A2 and apply the “Custom” format with the type “d “days,” h “hours,” m “minutes, and” s “seconds”” to the cell containing the time difference formula. |
| =IF(INT(B2-A2)>0,INT(B2-A2)&” days,”,””)&IF(HOUR(B2-A2)>0,HOUR(B2-A2)&” hours,”,””)&IF(MINUTE(B2-A2)>0,MINUTE(B2-A2)&” minutes, and “,””)&IF(SECOND(B2-A2)>0,SECOND(B2-A2)&” seconds”,””) | Time elapsed between the date and time or values in cells A2 and B2, expressed in the format “dd days, hh hours, mm minutes, and ss seconds” with zero values hidden. Warning: This formula returns a textual value. If you need the result to be a value, use the formula =B2-A2 and apply the “Custom” format with the type “d “days,” h “hours,” m “minutes, and” s “seconds”” to the cell containing the time difference formula. |
| =A2+TIME(1,0,0) | Value in cell A2 plus one hour. Warning: This formula only allows adding less than 24 hours to a value. |
| =A2+(30/24) | Value in cell A2 plus 30 hours (24 being hours in a day). Warning: This formula allows adding any number of hours to a value. |
| =A2-TIME(1,0,0) | Time value in cell A2 minus one hour. Warning: This formula only allows subtracting less than 24 hours from a value. |
| =A2-(30/24) | Time value in cell A2 minus 30 hours. Warning: This formula allows subtracting any number of hours from a value. |
| =A2+TIME(0,1,0) | Time value in cell A2 plus one minute. Warning: This formula only allows adding less than 60 minutes to a value. |
| =A2+(100/1440) | Time value in cell A2 plus 100 minutes (1440 being minutes in a day). Warning: This formula allows adding any number of minutes to a value. |
| =A2-TIME(0,1,0) | Time value in cell A2 minus one minute. Warning: This formula only allows subtracting less than 60 minutes from a value. |
| =A2-(100/1440) | Time value in cell A2 minus 100 minutes. Warning: This formula allows subtracting any number of minutes from a value. |
| =A2+TIME(0,0,1) | Time value in cell A2 plus one second. Warning: This formula only allows adding less than 60 seconds to a value. |
| =A2+(100/86400) | Time value in cell A2 plus 100 seconds (86400 being seconds in a day). Warning: This formula allows adding any number of seconds to a value. |
| =A2-TIME(0,0,1) | Time value in cell A2 minus one second. Warning: This formula only allows subtracting less than 60 seconds from a value. |
| =A2-(100/86400) | Time value in cell A2 minus 100 seconds. Warning: This formula allows subtracting any number of seconds from a value. |
| =A2+B2 =SUM(A2:B2) | Total hours, hours and minutes, or hours, minutes, and seconds in the values from cells A2 and B2, depending on the format you applied to those cells. Warning: If the total adds up to more than 24 hours, you must apply the “Custom” format with the type “[h]:mm;@” to the cell containing the SUM formula. |
Summarize this article for me pleaseAn Easier Alternative to Calculating Time in Excel
Although the formulas in this article work, there’s a better way than Excel to calculate time—ClickUp!
Sure, spreadsheets are great for managing data, but what about your day-to-day tasks? And how do you communicate with your team to show these times in a simple and clear way?
ClickUp’s Table view keeps track of all your tasks, including assignees, status, and due dates, with ease. Plus, the Table view lets you quickly check on the progress of each project task.
Use ClickUp’s date and time functions to easily make calculations for time worked.
But that’s not all—you can copy and paste your table into other software, pin specific columns, auto-size your columns, and even share your tables through a unique link.
This simple interface makes it a breeze to drag and drop rows and columns to quickly change your view.
ClickUp is an all-in-one management software for time tracking and figuring time estimates while supporting dates and times fields, so you can finally break up with Excel for your project management needs.
If you don’t want to start from scratch, try ClickUp’s Time Analysis Template to get started! This template allows you to monitor project timelines, track individual task times, and analyze time spent on each task or project for comprehensive oversight. By providing a clear and comprehensive view of time utilization, it helps drive productivity and ensures efficient resource allocation. Say goodbye to complicated Excel formulas and start optimizing your team’s time management with ClickUp!
Don’t believe us? Create your own table for free in ClickUp today!
Everything you need to stay organized and get work done.
