How to Fix Excel #VALUE! Error When Adding Times and Durations
Question details
The user encounters a #VALUE! error when attempting to add or calculate time and duration values in a spreadsheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding time-of-day or duration values copied from other sources or entered manually into a spreadsheet.
- Observed behavior
- Excel returns a #VALUE! error because one or more cells contain text disguised as time values, often due to hidden characters, incorrect duration formats, or regional setting mismatches.
Before applying formulas, visually inspect the problematic cells to see if the data aligns to the left (a common indicator of text) and verify if the data was recently copied from an external source like a webpage.
Clean Hidden Characters and Convert Text to Time Values
Use built-in functions to strip hidden web characters like non-breaking spaces and convert text strings into calculable numeric time formats.
Often, data copied from web pages includes non-breaking spaces that standard trimming functions cannot remove. This causes Excel to treat the cell contents as text, leading to the #VALUE! error during addition.
Select an empty cell and enter =ISTEXT(A1) (replace A1 with your time cell). If the formula returns TRUE, the spreadsheet is treating the time as text.
In a blank adjacent cell, enter the formula =--CLEAN(TRIM(SUBSTITUTE(A1, CHAR(160), ""))) and press Enter. The double negative (--) converts the cleaned text string into a numeric value.
Copy the newly converted cells, right-click the original time cells, click 'Paste Special', and select 'Values' to overwrite the text with actual numeric data.
Highlight the corrected cells, press Ctrl+1 to open the Format Cells dialog, navigate to the 'Custom' category, and enter [h]:mm or [m]:ss to correctly display durations.
Adjust Windows Regional Settings
Fix time recognition issues by ensuring your system's time separator format matches the format you type into your spreadsheet.
Easily Manage and Calculate Time Data in WPS Spreadsheet
WPS Spreadsheet provides intuitive data cleaning tools, such as intelligent Text-to-Columns, to effortlessly convert copied web data into calculation-ready time formats.
- 1. Open Your Document: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Select the Text-Based Times: Highlight the problematic time cells that are returning the #VALUE! error during addition.
- 3. Use Text to Columns: Navigate to the 'Data' tab and click 'Text to Columns'. Click through the wizard using default settings to let WPS automatically re-evaluate the text as time data.
- 4. Format for Duration: Right-click the converted cells, choose 'Format Cells', select 'Custom', and apply the [h]:mm format for accurate duration totals.
Frequently Asked Questions
Why does my time addition reset after 24 hours?
Standard time formats evaluate time-of-day, which naturally loops after 24 hours. To display cumulative durations (like totaling timesheets), apply a Custom Format and wrap the hour symbol in brackets, such as [h]:mm.
What does CHAR(160) do in the cleanup formula?
CHAR(160) represents a non-breaking space, which is frequently found in data copied from web pages. Standard TRIM functions do not remove this character, so SUBSTITUTE is used to replace it with a standard space before converting the text to a number.
Can simply changing the cell format from Text to Time fix the #VALUE! error?
No. Changing the cell format does not change the underlying data type. You must first convert the text string into a numeric value using a formula, the Text to Columns tool, or Paste Special before the new Time format will take effect.
How can I quickly check if my time data is being read as text or numbers?
By default, numeric time values automatically align to the right side of the cell, while text aligns to the left. You can also use the =ISTEXT(cell_reference) function; if it returns TRUE, your time is currently formatted as text.
