Why Excel Date Subtraction and TEXT Can Show an Unexpected Number in WPS Office Excel
Question details
The user wants to understand why two Excel date cells that appear to be the same day can produce an unexpected result when the subtraction is wrapped in the TEXT function.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Calculating or comparing dates that are displayed as the same day but may contain hidden time components.
- Observed behavior
- A formula such as TEXT(A1-B1, ...) shows an unexpected number instead of the expected date or day count.
Confirm that the source cells contain real Excel dates instead of text strings, and temporarily format the date cells as General to see whether they include hidden decimal time values.
Check hidden time values and use INT to calculate calendar days in Excel
Use this when two date cells show the same day but return an unexpected difference after applying TEXT.
Excel stores dates as serial numbers and times as decimal fractions. Two cells can display the same date while still containing different times, so subtracting them returns a fractional value that TEXT may display as an unexpected number.
Click the cell containing the formula =A1-B1, go to the Home tab, and open the Number Format drop-down. Choose General to view the underlying serial number or fractional day value.
Select cells A1 and B1, open Format Cells with Ctrl+1, and switch between Date and General. If the values include decimals such as 45292.5 and 45292.25, the cells contain different times.
Change the comparison formula to =INT(A1)=INT(B1) to test whether both cells fall on the same calendar date while ignoring the time portion.
Use =INT(A1)-INT(B1) as the elapsed-days formula, then apply TEXT only if a specific text output is needed; otherwise keep the cell as General or Number.
Convert text dates to real Excel dates before subtraction
Use this when date cells are stored as text strings, causing the subtraction or TEXT result to show a value error or an incorrect number.
Use INT Formulas and Text to Columns to Fix Date Subtraction in WPS Office Excel
Open the affected workbook in WPS Office Excel, inspect hidden date-time values, and apply Excel-compatible INT-based formulas or Text to Columns so date subtraction returns the correct calendar days.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office, click Menu, choose Open, and select the workbook that contains the date cells.
- 2. Inspect the date cells with General format: Select the two date cells, go to the Home tab, and choose General from the Number Format drop-down to reveal any hidden decimal time values.
- 3. Enter the INT-based calculation: Click an empty cell, type =INT(A1)-INT(B1), and press Enter to calculate whole calendar days without the time fraction.
- 4. Wrap the result in TEXT if required: If you need the result as text, enter =TEXT(INT(A1)-INT(B1),"0") and press Enter to return the day count as a text string.
Frequently Asked Questions
Why does =A1-B1 show a decimal like 0.004 when both cells show the same date?
Excel dates include time as a decimal fraction. If A1 and B1 look identical but contain different times, subtraction returns that fractional difference. Use =INT(A1)-INT(B1) to calculate whole calendar days.
How can I check whether a date cell also contains a time?
Select the cell and press Ctrl+1 to open Format Cells. Switch the number format to General. If the value appears as a decimal number, the cell contains both a date and a time.
Why does TEXT with a date format return an unexpected number instead of a date?
TEXT converts the numeric result using the format string. If the result is a fractional day or the format string does not include a suitable date/time token, the output can appear as an unexpected number. Inspect the raw result before applying TEXT.
How do I compare two dates while ignoring time?
Use =INT(A1)=INT(B1). This compares only the integer portion of each serial number and returns TRUE when both cells fall on the same calendar date.
Can WPS Office handle Excel date formulas like TEXT and INT?
Yes, WPS Office Excel supports Excel-compatible formulas and .xlsx files, so you can use the same TEXT, INT, and date subtraction formula syntax.
