How to Prevent Excel from Returning 01/01/1900 with IFS Formula
Question details
The user wants to stop the spreadsheet from returning the default date 01/01/1900 when using an IFS and EDATE formula that references an empty source cell, and instead display the custom text 'auto'.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating future dates based on a source date and specific month intervals using logical and date functions.
- Observed behavior
- When the source date cell is blank, the formula evaluates the blank as a zero, returning the default serial date 01/01/1900 instead of waiting for valid input.
Ensure you know the exact cell references for your source date and the interval values before modifying your formula. It is also helpful to verify that your target cell is formatted as a Date to display the final calculation correctly.
Use an IF Statement to Check for Empty Cells
By wrapping your IFS formula in a standard IF statement that checks for a blank source cell, you can force the application to return custom text instead of the 1900 date.
Spreadsheet programs process blank cells referenced in mathematical calculations as a zero. Since the date serial number system begins on January 1, 1900 (day 1), a zero value defaults to the start of that epoch (01/01/1900). Addressing the blank cell first resolves the root cause.
Click on the cell where you want your formula result to appear.
Begin by typing =IF(Q17="","auto", into the formula bar, replacing Q17 with the cell reference of your source date.
Append your existing IFERROR and IFS logic to the false-condition argument of the IF statement. For example: =IF(Q17="","auto",IFERROR(IFS(O17=3,EDATE(Q17,3),O17=6,EDATE(Q17,6),O17=12,EDATE(Q17,12)),"auto"))
Press the Enter key to apply the formula. Click and drag the fill handle at the bottom right corner of the cell to copy the new formula down your column.
Fix Formula Errors Seamlessly with WPS Spreadsheet
WPS Office provides a powerful, free spreadsheet tool that fully supports advanced logical functions like IF, IFS, IFERROR, and EDATE. You can easily troubleshoot formula issues and ensure your data logic works flawlessly.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your formula.
- 2. Locate the error cell: Double-click the cell returning the incorrect 01/01/1900 date to edit its formula.
- 3. Update the formula: Modify the formula to include the blank cell check by typing =IF(Q17="","auto", [your_original_logic]).
- 4. Confirm the fix: Press Enter, and WPS Spreadsheet will instantly update to show your desired text instead of an incorrect date.
Frequently Asked Questions
Why does Excel show 01/01/1900 when I link to an empty cell?
Spreadsheet calendar systems use serial numbers starting on January 1, 1900. When a formula references a blank cell for a date calculation, it treats the blank as a numeric zero. Therefore, day zero is translated and displayed as 01/01/1900.
Can I hide the 01/01/1900 date using custom cell formatting instead of a formula?
Yes. Select the cell, press Ctrl+1 to open Format Cells, navigate to Custom, and enter a format string like m/d/yyyy;;;@. This effectively hides zero values, though it will not allow you to output a custom replacement word like 'auto'.
Does the IFS function support checking for blank cells directly?
Yes, you can include Q17="","auto" as the first logical test in your IFS statement. However, wrapping the entire formula in a standard IF statement is often cleaner and prevents subsequent logical tests from returning errors.
What is the purpose of IFERROR in this specific formula?
IFERROR catches scenarios where the IFS function doesn't find any matching condition (for example, if the month interval entered is not 3, 6, or 12). It ensures the formula returns 'auto' instead of displaying an ugly #N/A error on your sheet.
