Adding time in Excel helps you calculate durations, track shifts, and manage schedules without manual math. This guide shows the most reliable formulas and settings so your clock values stay accurate and readable.
Use this summary to compare methods, outcomes, and best practices at a glance.
| Method | Formula Example | Result Format | Best For |
|---|---|---|---|
| Simple Addition | =A2+B2 | Short tasks on the same day | |
| SUM Function | =SUM(A2:A10) | h:mm or [h]:mm | Multiple time entries |
| TEXT for Concatenation | =A2&TIME(B2,0,0) | Custom display | Text to time conversion |
| Decimal to Time | =A2/24 | h:mm | Hours stored as numbers |
| Cross-Day Duration | =MOD(end-start,1) | [h]:mm | Shifts that span midnight |
Basic Time Addition Formula
For straightforward cases, use a simple plus sign between cell references containing time values. The standard format h:mm keeps hours under 24, which works when the total is less than one full day.
Ensure cells are formatted as time so Excel aligns hours, minutes, and seconds properly. Right-click the cell, choose Format Cells, pick Time, and pick a layout like 13:30 or 13:30:00.
When you add values that exceed 24 hours, switch to [h]:mm format to display hours beyond the 24-hour clock correctly.
Adding Multiple Time Values with SUM
Use the SUM function to add a range of time cells at once, which is efficient for logging hours in a timesheet.
- Select the total cell and apply time formatting.
- Enter =SUM(A2:A10) to add all entries in the range.
- If totals exceed 24 hours, set the cell format to [h]:mm.
Using SUM reduces the need for multiple manual formulas and helps maintain consistency across rows that track labor or event durations.
Handling Time Values Entered as Text
Sometimes time data appears as text, which prevents direct calculation. Convert text to a real time value using TIME functions or VALUE adjustments in such cases.
TIME Function for Separate Hour and Minute Columns
If your sheet lists hours and minutes in different columns, wrap them with TIME to build a valid time serial number for addition.
Converting Text with LEFT, MID, and RIGHT
Extract parts of a text string like "13:45" using text functions, then combine them into a TIME value for accurate calculations and reporting.
Working with Cross-Day and Overnight Shifts
When a shift crosses midnight, subtraction can return a negative or small decimal rather than the expected duration. The MOD function normalizes the result within a 24-hour cycle.
Wrap your calculation with MOD and use [h]:mm formatting on the result cell to display elapsed time correctly, even when end time is numerically smaller than start time.
Best Practices for Time Calculations
- Consistently use time formatting across all relevant cells.
- Choose [h]:mm for durations that may exceed 24 hours.
- Validate source data to avoid text that looks like time.
- Wrap cross-day logic with MOD to prevent negative results.
- Document the format and method directly in the worksheet or a helper column.
FAQ
Reader questions
Why does my sum of times show a wrong value or #####?
You likely need to change the cell format to [h]:mm or adjust the column width. Without the correct format, Excel may overflow the display or truncate values.
How do I add hours that total more than 24 hours?
Apply the [h]:mm custom format to the total cell so hours continue counting beyond 24 instead of resetting to zero.
Can I add start and end times directly to get duration?
Yes, subtracting start from end works when both times are on the same day. For overnight shifts, wrap the subtraction with MOD to handle negative results.
What if my time data is stored as numbers like 0.75?
Divide the number by 24 and format the cell as time to convert decimal hours into readable hours and minutes.