Forum Discussion
Date formula if dates in 1 to 4 cells
I want a formula to calculate the days (or months) an employee has worked in 2021 thus far (i.e. from TODAY'S date). An employee will have on their profile some or all of the following dates:
- Hire Date
- Rehire Date
- Adjusted Seniority Date
- Termination Date
If any of the above dates are prior to 2021, the formula needs to calculate from 01/01/2021 thru TODAY. And, the formula needs to work in order of the above list. Meaning, if an employee has Hire Date on their profile, use Hire Date (or 01/01/21 if Hire Date is < 01/01/21); if an employee has Hire Date and Rehire Date on their profile, use Rehire Date (or 01/01/21 if Rehire Date is < 01/01/21); if an employee has Hire Date, Rehire Date, and Adjusted Seniority Date, use Adjusted Seniority Date (or 01/01/21 if Adjusted Seniority Date is < 01/01/21); if an employee has Termination Date on their profile, use Termination Date (or 01/01/21 if Termination Date is < 01/01/21).
Possible?
2 Replies
Teresa Smagacz Is there a formula to determine if the employee was active in Q1 using the Hire Date, Regire Date, Prior Termination Date, Termination Date
2 Names with formula. Ideally you'll put them in the Name manager:
in B1 (Name = today): =TODAY()
in B2 (Name = minDate): =DATE(2021,1,1)
in F5:
=today - MAX(LOOKUP(2, 1/(B5:E5 <> ""), B5:E5), minDate)
