Excel Formula to Add 90 Days to a Hire or Rehire Date
Question details
The user needs a formula to calculate an employee's 90-day review eligibility date, which must default to the rehire date if available, or the original hire date if the rehire date is blank.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing HR records to track employee probationary periods and review eligibility.
- Observed behavior
- Requires a logical condition to dynamically check which date cell is populated before adding 90 days.
Ensure that your hire and rehire dates are properly formatted as 'Date' values in your spreadsheet, rather than plain text, so the mathematical addition of days works correctly.
Use the IF Function to Conditionally Add 90 Days
This method uses the IF function to evaluate whether a rehire date exists. If the rehire date cell is blank, it adds 90 days to the original hire date; otherwise, it adds 90 days to the rehire date.
In Excel, dates are stored as sequential serial numbers, meaning you can simply add a number (like 90) directly to a date cell to add that many days.
By combining this with an IF statement, you can automate the decision-making process for your HR roster without manually checking each row.
Locate the cells containing your dates. For this example, assume the Original Hire Date is in cell H2 and the Rehire Date is in cell R2.
Click on the cell where you want the Review Eligibility Date to appear.
Type the formula =IF(R2="",H2,R2)+90 and press Enter. This tells Excel: 'If R2 is empty, use H2, otherwise use R2, then add 90'.
Hover over the bottom-right corner of the cell until you see the Fill Handle (a small cross). Click and drag it down the column to apply the calculation to all employees.
Use the MAX Function as a Shortcut
If a rehire date is always mathematically greater (later) than the original hire date, you can use the MAX function to automatically select the most recent date.
Automate HR Calculations Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas and date calculations. You can seamlessly track employee reviews, manage rosters, and automate dates with the exact same IF and MAX functions used in Excel.
- 1. Open your HR spreadsheet: Launch WPS Office and open your .xlsx or .csv employee roster file.
- 2. Enter the date formula: Click the cell for the review date and type =IF(R2="",H2,R2)+90.
- 3. Drag to fill: Press Enter, click the cell again, and drag the fill handle down to calculate dates for your entire workforce.
Frequently Asked Questions
How do I add 90 working days instead of calendar days?
To add only business days (skipping weekends), you need to use the WORKDAY function. The formula would be =WORKDAY(IF(R2="",H2,R2), 90). You can also add a range of holidays as an optional third argument to skip those as well.
Why does my formula return a #VALUE! error?
This error occurs if the hire or rehire date cells contain text instead of valid dates. Check for accidental hidden spaces in your blank cells, or ensure the cells are properly formatted as Dates.
Can I add exactly 3 months instead of 90 days?
Yes, to add exactly 3 calendar months regardless of how many days are in those months, use the EDATE function. The formula is =EDATE(IF(R2="",H2,R2), 3).
