Formula for date range after two different dates

mreynolds66

I have officially been stumped on formulas and need to know if its even possible at this point.

I have an audit on documents where I have a Hire_Date, Rehire_Date, Termindation_Date, and a Purge Date.

The purge date needs to equal the month/day from the Hire_Date column OR the Rehire_Date (if filled in), but three years past the year within either column (see example below).

If this is not possible, that is ok, I just do not want to put more hours into trying to figure this out.

Gillian C

Hi @mreynolds66

So I have my date format set up for UK format DD/MM/YY but the formula that I would use is

=IFERROR(IF([Rehire_Date]@row <> "", DATE((YEAR([Rehire_Date]@row) + 3), MONTH([Rehire_Date]@row), DAY([Rehire_Date]@row)), DATE((YEAR([Hire_Date]@row) + 3), MONTH([Hire_Date]@row), DAY([Hire_Date]@row))), "")

Basically saying if the [Rehire_Date] column is anything other than a blank cell the date will equal DATE( Year, Month, Date) using the [Rehire_Date] column as your date starting point, and then else DATE(Year, Month, Date) again but this time using the [Hire_Date] as your date starting point.

The only reason the IFFERROR( ,"") is in there is just incase you have row entries without any of these dates in it. If you are always going to have those rows then you can change the formula to

=IF([Rehire_Date]@row <> "", DATE((YEAR([Rehire_Date]@row) + 3), MONTH([Rehire_Date]@row), DAY([Rehire_Date]@row)), DATE((YEAR([Hire_Date]@row) + 3), MONTH([Hire_Date]@row), DAY([Hire_Date]@row)))

Please double check if the date format changes the DATE(Year, Month, Date) function format

Gillian C

Hi @mreynolds66

So I have my date format set up for UK format DD/MM/YY but the formula that I would use is

=IFERROR(IF([Rehire_Date]@row <> "", DATE((YEAR([Rehire_Date]@row) + 3), MONTH([Rehire_Date]@row), DAY([Rehire_Date]@row)), DATE((YEAR([Hire_Date]@row) + 3), MONTH([Hire_Date]@row), DAY([Hire_Date]@row))), "")

Basically saying if the [Rehire_Date] column is anything other than a blank cell the date will equal DATE( Year, Month, Date) using the [Rehire_Date] column as your date starting point, and then else DATE(Year, Month, Date) again but this time using the [Hire_Date] as your date starting point.

The only reason the IFFERROR( ,"") is in there is just incase you have row entries without any of these dates in it. If you are always going to have those rows then you can change the formula to

=IF([Rehire_Date]@row <> "", DATE((YEAR([Rehire_Date]@row) + 3), MONTH([Rehire_Date]@row), DAY([Rehire_Date]@row)), DATE((YEAR([Hire_Date]@row) + 3), MONTH([Hire_Date]@row), DAY([Hire_Date]@row)))

Please double check if the date format changes the DATE(Year, Month, Date) function format

mreynolds66

https://community.smartsheet.com/discussion/comment/482438#Comment_482438

You are amazing!! Thank you so much!

Gillian C

@mreynolds66

Super, glad it helped 😊