Calculation Issues

How to Add Times and Calculate Elapsed Time in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to calculate the elapsed time between a start time and an end time, and add it to other time values, but encounters a calculation error when adding a text-formatted duration to a numeric time value.

Product: Excel
Device & OS: not provided
Scenario: Creating an itinerary or tracking work hours where start and end times are subtracted to find duration, and durations are subsequently added together.
Observed behavior: Adding a text-based formula result that displays elapsed hours and minutes to a standard numeric time value results in a calculation error, preventing further time math.

Before you start

Ensure that the cells containing your start and end times are formatted as Time or Custom (h:mm AM/PM) rather than General or Text to prevent underlying calculation conflicts.

Solution 1Recommended

Calculate Elapsed Time Numerically with the MOD Function

Use the MOD function to keep the elapsed time as a numeric value, avoiding #VALUE! errors when adding times together.

Excel stores times as fractional days. When you use text-based formulas to extract hours and minutes, the result becomes a text string and cannot be mathematically added to other time values. Using the MOD function ensures the duration remains a numeric value that Excel can recognize and add.

Select the elapsed time cell

Click on the cell where you want to display the calculated elapsed time (e.g., D47).

Enter the MOD formula

Type the formula =MOD(C47-B47,1), where C47 is your end time and B47 is your start time, then press Enter.

Open Format Cells

Right-click the cell containing your new formula and select "Format Cells" from the context menu.

Apply Custom Time Format

Navigate to the Custom category. Enter [h]:mm for total elapsed hours, or h:mm AM/PM if the final value must reflect a specific itinerary time, and click OK.

Handling Overnight Times: Using =MOD(End-Start, 1) has the added benefit of perfectly calculating overnight shifts that cross past midnight without returning negative errors.

Easily Calculate Time in Spreadsheets

Use WPS Spreadsheet for Accurate Time Tracking

WPS Office provides robust spreadsheet tools with full compatibility for all Excel formulas, including MOD and advanced time formatting, allowing you to build itineraries and time-tracking sheets effortlessly.

1. Open your time-tracking workbook: Launch WPS Spreadsheet and open your existing itinerary or timesheet file.
2. Calculate the duration: Use the =MOD(End_Time - Start_Time, 1) formula to find the exact duration numerically.
3. Format the output: Press Ctrl+1 to open the Format Cells dialog and apply a custom time format like [h]:mm to calculate cumulative hours correctly.

Fully compatible with Microsoft Excel time formats and formulasFree and lightweight alternative for spreadsheet data analysisIntuitive format cells dialog to easily customize time and date displays

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when adding elapsed time?

This usually happens because the elapsed time was calculated using a text formula (like the TEXT function or string concatenation). Excel cannot mathematically add text characters to a numeric time value. You must calculate the duration numerically.

How do I calculate time differences that cross midnight?

The =MOD(End_Time - Start_Time, 1) formula handles overnight shifts naturally. The MOD function returns the correct positive time difference even if the end time is mathematically smaller (earlier in the day) than the start time.

How can I add total hours so that they exceed 24 hours?

When summing multiple durations, format the total cell using the Custom format [h]:mm. The square brackets tell the spreadsheet to display total elapsed hours cumulatively rather than resetting the clock to zero every 24 hours.

Excel & Data Problems