Fix Excel TODAY Function Returning Zero or Negative Dates
Question details
The user needs to correct a date calculation formula using the TODAY function that is returning an unexpected zero or negative date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the difference between two dates conditionally, depending on whether a specific target cell is empty or contains a date.
- Observed behavior
- Excel evaluates an empty date cell as zero because the logical formula tests for a space character instead of an empty string, executing the wrong branch of the IF statement.
Double-check your referenced cells (e.g., C14 and B14) to ensure they actually contain valid dates or are completely blank without hidden spaces.
Replace the Space with an Empty String in the IF Formula
Correct the logical condition in the IF formula by removing the space between the quotation marks to properly check for a blank cell.
In Excel formulas, two quotation marks with a space between them (" ") check for a literal space character. To check if a cell is truly empty, you must use two quotation marks with nothing in between ("").
Click on the cell that contains the formula returning the negative date or zero.
Click inside the Formula Bar at the top of the worksheet to edit the formula.
Locate the condition testing for a space, such as C14=" ", and delete the space so it becomes C14="".
Ensure your full formula looks like =IF(C14="",TODAY()-B14,C14-B14) and press Enter.
Use Evaluate Formula to Troubleshoot Logic
Use Excel's built-in evaluation tool to step through the formula and identify which branch of the IF statement is being executed.
Calculate Date Differences Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and date functions, allowing you to accurately track schedules and manage time calculations without syntax issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date data.
- 2. Input the exact formula: Type =IF(C14="",TODAY(),C14)-B14 into the target cell to calculate the date difference.
- 3. Adjust the cell format: If the result displays as a strange date instead of a number, right-click the cell, select 'Format Cells', and change the category to 'Number' or 'General'.
Frequently Asked Questions
Why does my date formula return 1/0/1900 instead of a number?
If a date calculation returns 1/0/1900 or a similar date, the cell format is incorrectly set to 'Date' instead of 'Number'. To fix this, right-click the cell, select 'Format Cells', and change the number format category to 'Number' or 'General'.
Can I use the DATEDIF function instead of subtracting dates directly?
Yes, you can use the syntax =DATEDIF(start_date, end_date, "d") to find the number of days between two dates. However, you will still need to wrap it in the correct IF logic to prevent errors when cells are empty.
What is the difference between "" and " " in Excel formulas?
"" (no space) represents an empty string or a truly blank cell. In contrast, " " (with a space) represents a text string containing a single space character. Testing for " " will fail if the target cell is actually empty.
