Lets say that you want find out how long it takes for an employee to complete an assembly line operation or a fast food order to be processed at peak hours. There are several ways to calculate the difference between two times.
As a spreadsheet power user you likely find yourself needing to calculate time in Excel frequently. Luckily, Excel provides several straightforward functions to add subtract, and calculate time values in your worksheets.
In this comprehensive guide I’ll walk through the most common time calculations you can perform in Excel using formulas. We’ll cover
- Calculating the time between two times
- Adding and subtracting time values
- Converting text timestamps to time values
- Extracting hour, minute, and second values
- Rounding time values up or down
By the end, you’ll have a toolkit of time calculation formulas and understand exactly when to use each one. Let’s dive in!
Calculating Elapsed Time Between Two Times
One of the most frequent time calculations is finding the time difference between two date/time values. For example, calculating the time spent on a project from start to finish.
Excel provides a simple dedicated function to calculate the span between two times, in days, hours, minutes or seconds.
To find time elapsed:
-
Enter the two date/time values in separate cells. For example the starting time in A1 and ending time in B1.
-
In another cell, use the formula =B1-A1, replacing B1 and A1 with your cells.
-
By default the result is shown in days. To view the elapsed time in hours, minutes or seconds, wrap the formula in the TEXT function like:
=TEXT(B1-A1,"h:mm") → shows hours and minutes =TEXT(B1-A1,"h:mm:ss") → shows hours, minutes and secondsThe TEXT function lets you format the result as you need it.
Examples:
Start time: 1/1/2023 8:00 AM End time: 1/1/2023 5:30 PM =B1-A1 → 0.6875 (days)=TEXT(B1-A1,"h:mm") → 9:30 (hours and minutes) This makes calculating time spans a breeze in Excel!
Adding and Subtracting Time Values
In addition to finding the total time between two timestamps, you may need to add or subtract specific time amounts like hours and minutes.
For example, you may want to calculate end times by adding durations to start times, or adjust times by subtracting lunch breaks and the like.
Fortunately, Excel has + and – operators that work directly with time values:
Cell A1 = Start time Cell B1 = Duration to addTo calculate end time: =A1+B1Cell C1 = Adjusted timeCell D1 = Time to subtract To calculate adjusted time:=C1-D1When adding or subtracting, Excel automatically handles the carryover between seconds, minutes, hours, and days. Some examples:
8:00 AM + 2:30 hours → 10:30 AM3:45 PM - 1:15 hours → 2:30 PM 1/1/2023 5:00 PM + 4 days → 1/5/2023 5:00 PMSo adding and subtracting time values is just as easy as normal math operations in Excel.
Converting Text Strings to Time Values
Sometimes you may have timestamp data in text format rather than structured time values. For example:
9:30 AM12:45:15 PMBefore you can perform calculations with these timestamps, you’ll need to convert the text strings into Excel time values.
The VALUE function helps us convert text to times:
A1: 9:30 AMB1: 12:45:15 PM =VALUE(A1) → 9:30 AM (time value)=VALUE(B1) → 12:45:15 PM (time value)This function interprets the text timestamp and transforms it into a proper time value that Excel can work with.
A few notes on using VALUE:
-
Make sure text timestamps are formatted correctly, like “9:00 AM” or “3:45:15 PM”. VALUE won’t work with invalid formats.
-
The result will be formatted based on your system time settings. You may need to apply a custom time format after.
-
VALUE only works for simple timestamps, not full date/times like “March 5, 2023 9:30 AM”.
Once converted to time values, you can move forward with other time calculations as covered in this guide.
Extracting Hours, Minutes, or Seconds from Times
When working with time values in Excel, you can extract just the hour, minute, or second component using the HOUR, MINUTE, and SECOND functions:
To extract hours:
A1: 3:45 PM =HOUR(A1) → 15 (using 24hr time)To extract minutes:
A1: 12:45:15 PM=MINUTE(A1) → 45To extract seconds:
A1: 9:30:55 AM =SECOND(A1) → 55This can be helpful in many scenarios like:
- Summarizing hours worked per day from time records
- Calculating billing minutes from start and end times
- Checking if a time value contains seconds or is rounded to the minute
Together with other time functions, you can build complete time tracking and reporting capabilities in your worksheets.
Rounding Time Values Up or Down
Because time can be tracked in Excel down to the second, you may end up with time values like 3:52:36 PM that are awkward to work with.
In many cases, you’ll want to round the times to a cleaner format like the nearest minute, quarter hour, half hour, or hour.
The MROUND function allows us to easily round a time value up or down:
To round up:
A1: 3:52:36 PM=MROUND(A1, "5 minutes") → 3:55 PM Rounds up to nearest 5 minutes.To round down:
A1: 8:47:00 AM =MROUND(A1, "15 minutes", -1) → 8:45 AM-1 rounds down rather than up.The -1 option is key for rounding down instead of Excel’s default round up.
Some examples of handy increments you can round to:
- 15 minutes
- 20 minutes
- 30 minutes
- 1 hour
MROUND gives you full control over rounding to clean time values.
Interactive Time Calculation Examples
To help you explore working with times in Excel, I created a sample interactive workbook you can access here.
It includes examples of:
- Calculating elapsed time
- Adding and subtracting times
- Converting text to times
- Extracting time units
- Rounding times
Feel free to copy and try the formulas yourself on the sample data provided!
Common Time Calculations and Use Cases
Now that you’re armed with Excel’s key time functions, here are some of the most popular time calculations and scenarios where they come in handy:
-
Employee or equipment usage tracking – Log start and end times then calculate durations to track utilization.
-
Scheduling and calendars – Add and subtract durations from timestamps to plot schedules.
-
Billing and timecards – Determine billable time from timestamps and round to increment.
-
Project management – Calculate elapsed time across tasks to track project progress.
-
Sports and analytics – Determine game times by adding set durations to start times.
-
Scientific data – Analyze precise timestamps from experiment data.
-
Delivery and logistics – Subtract pickup times from dropoff times to determine travel time.
The possibilities are endless! Whatever your needs, Excel has you covered for manipulating time values.
Quick Reference of Key Excel Time Functions
For easy reference, here are the core time functions covered in one place:
Calculate elapsed time between two times:
=B1-A1 Format with TEXT():=TEXT(B1-A1,"h:mm:ss")Add or subtract time values:
Start time + Duration =A1+B1Time - Break duration=A1-B1 Convert text string to time value:
=VALUE(A1)Extract hour, minute or second:
=HOUR(A1)=MINUTE
Present the result in the standard time format
There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). You use the subtraction operator (–) to find the difference between times, and then do either of the following:
Apply a custom format code to the cell by doing the following:
- Select the cell.
- On the Home tab, in the Number group, click the arrow next to the General box, and then click More Number Formats.
- In the Format Cells dialog box, click Custom in the Category list, and then select a custom format in the Type box.
Use the TEXT function to format the times: When you use the time format codes, hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.
Example Table 1 — Present the result in the standard time format
Copy the following table to a blank worksheet, and then modify if necessary.
|
A |
B |
|
|---|---|---|
|
1 |
Start time |
End time |
|
2 |
6/9/2007 10:35 AM |
6/9/2007 3:30 PM |
|
3 |
Formula |
Description (Result) |
|
4 |
=B2-A2 |
Hours between two times (4). You must manually apply the custom format “h” to the cell. |
|
5 |
=B2-A2 |
Hours and minutes between two times (4:55). You must manually apply the custom format “h:mm” to the cell. |
|
6 |
=B2-A2 |
Hours, minutes, and seconds between two times (4:55:00). You must manually apply the custom format “h:mm:ss” to the cell. |
|
7 |
=TEXT(B2-A2,”h”) |
Hours between two times with the cell formatted as “h” by using the TEXT function (4). |
|
8 |
=TEXT(B2-A2,”h:mm”) |
Hours and minutes between two times with the cell formatted as “h:mm” by using the TEXT function (4:55). |
|
9 |
=TEXT(B2-A2,”h:mm:ss”) |
Hours, minutes, and seconds between two times with the cell formatted as “h:mm:ss” by using the TEXT function (4:55:00). |
Note: If you use both a format applied with the TEXT function and apply a number format to the cell, the TEXT function takes precedence over the cell formatting.
For more information about how to use these functions, see TEXT function and Display numbers as dates or times.
How to Calculate Hours Worked in Excel
How do you calculate time difference in Excel?
When you subtract the time values, Excel returns a decimal number that represents the resulting time difference. Since every whole number represents one day, the decimal part of the number would represent that much part of the day which can easily be converted into hours or minutes, or seconds.
How to add and subtract time in Excel?
Basically, there are 2 ways to add and subtract time in Excel: The TIME(hour, minute, second) function makes Excel time calculations really easy, however it does not allow adding or subtracting more than 23 hours, or 59 minutes, or 59 seconds.
How to calculate time out of time in Excel?
Let’s calculate the time out of it. Step 1) Write the TIME function as follows: Step 2) Refer to the value of the hour as the first argument. Step 3) Refer to the value of minutes as the second argument. Step 4) Refer to the value of seconds as the third argument. Step 5) Press enter. Excel calculates the time as 12:59 PM.
How to display time as more than 24 hours in Excel?
To display the time as more than 24 hours, select cell B4. Go to Home > Format, and then choose Format Cells. In the Format Cells box, choose Custom in the Category list. In the Type box, at the top of the list of formats, type [h]:mm;@ and then select OK. The result is 28 hours and 15 minutes.
