How to Use One Date or Add 365 Days to Another in Excel
Question details
The user needs an Excel formula to conditionally return a Projected Due Date, or add 365 days to an Approval Date if the projected date is blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a final due date based on whether a primary projected date is provided; if not, defaulting to one year after a secondary approval date.
- Observed behavior
- The formula needs to correctly return the exact date from the first referenced cell if populated, or accurately add 365 days to the second cell. Previously attempted formulas returned incorrect dates or failed to handle blank cells properly.
Ensure that the cells containing your dates are formatted as 'Date' (Short Date or Long Date) in Excel so that adding numerical values correctly increments the days.
Use a Nested IF Formula to Calculate Conditional Dates
By using nested IF statements, you can evaluate whether the primary date cell is empty. If it contains a date, it is returned directly; if not, the formula calculates the alternative date while preventing errors from blank cells.
This method checks multiple conditions sequentially. It first verifies if the Projected Due Date is blank. If true, it then checks if the Approval Date is also blank to prevent calculating a default 1900 date. Finally, it adds 365 days only if a valid Approval Date is present.
Click on the cell where you want the final calculated date to appear (for example, cell P2).
Type the formula `=IF(G2="",IF(H2="","",H2+365),G2+0)` into the formula bar. In this example, G2 represents your Projected Due Date and H2 represents your Approval Date.
Press Enter to execute the formula. Then, click and drag the fill handle at the bottom-right corner of the cell to copy the formula down to the remaining rows in your table.
Easily Manage Complex Date Formulas with WPS Office
WPS Spreadsheet provides comprehensive support for logical formulas like IF and conditional date calculations, giving you a powerful tool for complex data tracking without any hassle.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your date columns.
- 2. Enter the conditional formula: Select the target cell and enter the identical nested IF formula used in Excel to evaluate your dates.
- 3. Format as date: If the result appears as a five-digit number, right-click the cell, select 'Format Cells', and apply the 'Date' format.
- 4. Fill the column: Double-click the fill handle in the lower-right corner of the cell to instantly apply the calculation to your entire data table.
Frequently Asked Questions
Why does my date formula return a random 5-digit number?
In spreadsheet programs, dates are stored as serial numbers starting from January 1, 1900. If your formula returns a number like 44567, you simply need to change the cell format to 'Date' via the Home tab.
How can I add exactly one year instead of 365 days to account for leap years?
To add exactly one year regardless of leap years, use the EDATE function. You can modify your formula to `=IF(G2="",IF(H2="","",EDATE(H2, 12)),G2+0)`.
Why does my formula return 'January 0, 1900' when both date cells are blank?
This happens because adding a number to an empty cell causes the spreadsheet to treat the blank cell as zero. The nested `IF(H2="","",...)` statement prevents this by returning a true blank when no approval date is present.
