Calculation Issues

How to Add an Excel Duration to an End Time

Camila Milosovich Sep 28, 2026 868 views

Question details

The user needs to calculate an itinerary schedule by adding a calculated time duration to a previous end time, but the formula fails because the duration is formatted as text.

How to Add an Excel Duration to an End Time

Product: Excel
Device & OS: not provided
Scenario: Creating a sequential itinerary where the next start or end time is projected by adding a specific time duration to the previous time block.
Observed behavior: Adding a text-based duration (created using concatenation like HOUR() and MINUTE()) to a numeric time value results in an error because arithmetic operations cannot process text strings.

Before you start

Ensure that your base start and end times are entered as standard numeric time values (e.g., '8:00 AM') and not as plain text strings.

Solution 1Recommended

Calculate Duration as a Numeric Time Value

Avoid converting time differences into text strings. Calculate the duration as a standard time serial number so it can be added directly to other times.

Excel stores dates and times as numeric serial numbers. When you use text concatenation formulas (like joining 'HR' and 'MIN' to the numbers), Excel converts the underlying number into a text string. Text strings cannot be used in mathematical equations, which is why your addition formula fails.

Calculate the numeric duration

Instead of using text concatenation formulas, calculate the duration by simply subtracting the start time from the end time (e.g., =C47-B47).

Add the duration to your target time

Add the calculated duration to your target end time using a standard arithmetic formula. For example, use =C47+(C47-B47) to project the next time slot.

Apply custom time formatting

Right-click the cell containing your final formula, select 'Format Cells', choose the 'Custom' category, and enter 'h:mm AM/PM' to display the itinerary time correctly.

Calculate Duration as a Numeric Time Value

Avoid Text Formulas in Calculations: Formulas that result in text, such as =HOUR(A1)&" HR "&MINUTE(A1)&" MIN", are strictly for visual display and will break any subsequent arithmetic operations.

Solve Time Calculations in WPS

Easily Calculate Time and Durations in WPS Spreadsheet

WPS Spreadsheet fully supports advanced time calculations and custom formatting. You can easily add, subtract, and format time serial numbers for your itineraries without encountering text-to-number calculation errors.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your itinerary data.
2. Enter the time formula: Select the cell where you want the new projected time to appear and enter your numeric formula (e.g., =C47+(C47-B47)).
3. Access cell formatting: Press Ctrl+1 on your keyboard, or right-click the cell and select 'Format Cells' from the context menu.
4. Apply the time format: Navigate to the Custom category, type 'h:mm AM/PM' into the format box, and click OK to apply.

Fully compatible with Microsoft Excel time formatting and formulas (h:mm AM/PM)Built-in custom formatting tools for intuitive itinerary creationAutomatic error checking for text-versus-number calculation issuesFree and lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs when you try to add a text string to a numeric time value. Excel and WPS Spreadsheet require both values to be numeric time serial numbers to perform arithmetic. Ensure your duration is calculated numerically (e.g., End Time - Start Time) rather than formatted as text.

How do I display a duration that exceeds 24 hours?

To display durations longer than 24 hours without the time rolling over back to zero, you need to use square brackets around the hour in your custom format. Right-click the cell, select Format Cells, and apply the custom format [h]:mm.

Can I extract just the hours from a time duration?

Yes, you can use the =HOUR() function to extract the hour portion of a numeric time value. However, remember that using this function returns a standard integer, not a time serial number, so it should be used cautiously if further time arithmetic is required.

Excel & Data Problems