Time calculations in Excel can be incredibly useful for tracking work hours, calculating deadlines, or even figuring out the duration between events. Excel may seem intimidating for time-based math (versus standard numerical and decimal math), but it contains a variety of built-in functions to make things easier. For example…
Basic Time Entry and Formatting
Before diving into the more advanced stuff, it’s important to know how to input and format time in Excel properly. Excel recognizes time values in hh:mm:ss format.
- Entering time: You can enter time as 12:30 PM or 15:45 (for 3:45 PM) and Excel will automatically recognize these as time values and format accordingly.
- Formatting time: If Excel doesn’t automatically recognize your entry as time, you can change the format manually. Right-click the cell, select Format Cells > Time, and choose the appropriate format (e.g., 13:30 or 1:30 PM).
Note, by default, Excel may add a “PM” or “AM to the results of some of these results if your formatted numbers you’re calculating on are formatted that way. If you’re just looking for the time duration, that can be ignored.
Adding and Subtracting Time
Excel makes it easy to add or subtract time values. They work very similar to standard calculations, as long as your cells are formatted properly.
Example 1: Adding Time
If you want to calculate the end time of a project that starts at 9:00 AM and takes 3 hours and 30 minutes, you can simply add these values together:
=A1 + B1
Where:
- A1 contains the start time (e.g., 9:00 AM)
- B1 contains the duration (e.g., 3:30 for 3 hours and 30 minutes)
The result will be 12:30 PM.
Example 2: Subtracting Time
To calculate the duration between two times (say your start time was 9:00 AM and end time 2:15 PM), you subtract the earlier time from the later time:
=B1 - A1
Where:
- A1 contains 9:00 AM
- B1 contains 2:15 PM
The result will be 5:15 (5 hours and 15 minutes).
Calculating Elapsed Time Overnight
If you’re working with times that span past midnight, Excel can handle that too with a bit of work. Let’s say you have a start time on one day and an end time the next day. You can simply subtract the start time from the end time like before, but you may need to add 1 day for times that overlap midnight. For example:
=B1 - A1 + IF(B1<A1,1,0)
This formula accounts for times that pass midnight by adding 1 if the end time (B1) is earlier than the start time (A1). Otherwise it’ll work just fine with standard same-day calculations.
Converting Time to Decimal Hours
Sometimes, you need to convert time to a decimal format (very common for payroll calculations as an example and makes things far more straight forward for that type of thing). To convert time to hours or minutes, you can multiply by 24 or 1440, respectively.
Example 1: Convert Time to Decimal Hours
To convert 3:30 (3 hours and 30 minutes) into decimal hours:
=A1*24
Where A1 contains 3:30. The result will be 3.5 hours.
Example 2: Convert Time to Decimal Minutes
To convert 2:45 (2 hours and 45 minutes) into minutes:
=A1*1440
Where A1 contains 2:45. The result will be 165 minutes.
Once you have things into decimal formats, it makes running the numbers through other Excel built-in formulas much easier.
Handling Negative Time Results
Occasionally, Excel might show #### errors when subtracting times and getting a negative result. To handle negative times, go to File > Options > Advanced, scroll down to When calculating this workbook, and check the option for Use 1904 date system. You can read more about the two different date systems in Excel on Microsoft’s site. This change allows Excel to display negative times correctly.