How to Hide a Calculated Review Date When Hire Date is Blank in Excel
Question details
Prevent Excel from displaying a default 1900 date in a calculated cell when the initial reference date cell is empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a future date, such as a 90-day review date, based on an initial date like a hire date that may not be filled out yet.
- Observed behavior
- When adding days to an empty cell, Excel treats the blank cell as a zero and outputs a date like 3/30/1900 instead of leaving the calculation cell blank.
Identify the exact cell reference (e.g., B2) or the structured table column name (e.g., [@Hire_Date]) of your initial date before editing the formula.
Use the IF Function with Standard Cell References
Wrap your date addition formula in an IF statement to check if the source cell is empty before performing the calculation.
By default, Excel interprets a blank cell as the number 0. In Excel's date system, day 0 is January 0, 1900. When you add 90 days to a blank cell, Excel returns the 90th day of 1900, which is March 30, 1900. Using an IF function forces Excel to return a blank text string if the reference cell is empty.
Click on the cell where you want the calculated review date to appear.
Type =IF(B2="","",B2+90) into the formula bar. Replace 'B2' with your actual Hire Date cell reference, and '90' with the number of days you want to add.
Press Enter. If B2 is empty, the cell will remain blank. If B2 contains a date, the new calculated date will display.
Click the small square at the bottom-right corner of the cell and drag it down to apply this logic to the rest of the column.
Use the IF Function with Structured Table References
If your data is formatted as an Excel Table, use structured references to make your formulas dynamic and easier to read.
Calculate and Hide Dates Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas like the IF function, allowing you to create clean, professional data tables without formatting errors like 1/0/1900.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Select the calculation cell: Click the cell where the calculated review date should be displayed.
- 3. Input the IF formula: Type the formula =IF(B2="","",B2+90) into the formula bar.
- 4. Apply to the column: Press Enter and drag the fill handle to apply the formula across the column, automatically hiding dates for blank rows.
Frequently Asked Questions
Why does Excel show 3/30/1900 when referencing a blank cell?
Excel stores dates as sequential serial numbers starting from January 1, 1900. When a blank cell is referenced in a calculation, Excel treats its value as zero. Adding 90 days to zero returns the 90th day of 1900, which evaluates to March 30, 1900.
Can I use conditional formatting instead of a formula to hide the 1900 dates?
Yes, you can create a Conditional Formatting rule to set the font color to white if the cell value equals exactly 3/30/1900. However, using the IF function is generally a better practice because it prevents underlying erroneous data from causing errors in subsequent calculations or charts.
What if my base date is not blank, but contains text by mistake?
If the source cell mistakenly contains text instead of a valid date or a blank value, adding 90 to it will result in a #VALUE! error. You can handle this by wrapping your calculation in an IFERROR function, such as =IFERROR(B2+90, "").
