Fix an Excel EDATE IF Formula Showing January 1900 Instead of a Date
Question details
The user needs to prevent an IF formula containing EDATE from showing a date near January 1900 when the result should not yet be displayed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet calculates a future date from the date in A3 and a month interval, then tests whether that date has been reached.
- Observed behavior
- When the IF condition is false, the formula returns 0. Because the result cell is formatted as a date, Excel displays zero as a date near January 1900.
Select the source cell A3 and confirm that it contains a real date value, not date-looking text. Also check the regional date format so the entered day, month, and year are interpreted correctly.
Return a blank when the EDATE condition is false
Use this formula when the result cell should remain empty until today's date reaches the calculated EDATE value.
Excel stores dates as serial numbers, so a numeric zero in a date-formatted cell appears as a date near January 1900. Returning an empty string prevents that date display.
Click the cell that currently contains the IF and EDATE formula, then click in the Formula Bar.
Enter `=IF(TODAY()>=EDATE($A3,B$2*12),EDATE($A3,B$2*12),"")` and press Enter. The final `""` returns a blank instead of 0 when the condition is false.
Check whether `TODAY()` is on or after the date calculated by `EDATE($A3,B$2*12)`. The calculated date will appear only after that condition is true.
Verify the source date and regional date format
Use this method when the formula becomes true at the wrong time or EDATE calculates an unexpected date.
Use WPS Spreadsheet to return blank EDATE results instead of January 1900 dates
WPS Spreadsheet supports EDATE, IF, TODAY, and date number formats, so you can calculate due dates and keep the result cell blank until the date condition is met. Open the workbook, update the formula, and verify the source date directly in the familiar spreadsheet interface.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the Excel workbook containing the EDATE formula.
- 2. Select the affected result cell: Click the cell showing a January 1900 date and use the Formula Bar to review its IF formula.
- 3. Set the false IF result to blank: Replace the false argument with `""`, for example: `=IF(TODAY()>=EDATE($A3,B$2*12),EDATE($A3,B$2*12),"")`.
- 4. Check the source date cell: Select A3, then use Home > Number Format to apply a date format and confirm the displayed date is correct.
Frequently Asked Questions
Why does my EDATE formula show a date in January 1900?
The formula is returning 0 while the result cell uses a date format. Spreadsheet programs display date serial number 0 as a date near January 1900.
How do I make an IF formula show a blank instead of zero?
Use an empty string as the false result, such as `=IF(condition,value,"")`. In this case, use `=IF(TODAY()>=EDATE($A3,B$2*12),EDATE($A3,B$2*12),"")`.
What does EDATE($A3,B$2*12) calculate?
It returns the date that is the number of months in `B2*12` after the date in A3. If B2 contains years, multiplying by 12 converts those years to months.
How can I tell whether A3 is a real date instead of text?
Click A3 and inspect the Formula Bar, then apply Home > Number Format > Short Date. If formatting does not change a date-looking entry, re-enter it in the date order required by your regional settings.
Can WPS Spreadsheet open an Excel file with EDATE formulas?
Yes. WPS Spreadsheet can open and edit Microsoft Excel .xlsx and .xls workbooks and supports common functions such as IF, EDATE, and TODAY.
