Calculating a review date based on both the hire date and last date of review

Dan Y

So I've burned up about 6 weekends working on this, and have asked a few peripheral questions on it, but never just asked the whole thing. I met with a Smartsheet pro and he suggested I bring this to the community. I would really appreciate some help on this.

I need a formula that will calculate the next review date relative to the hire date, based on the date of last review. Seemed simple enough when I told my boss I could do it months ago…

Here's the current formula, which works, but changes things relative to the date of last review instead of hire date. This causes the next review date to shift when the review isn't done exactly on the date.

=IF([Employment Status]@row <> "Active", "", IF([Date of Last Review]@row = "", [Hire Date]@row + 60, IF(AND([Date of Last Review]@row < [Hire Date]@row + 90, [PT/FT]@row = "Full Time", Position@row <> ""), [Hire Date]@row + 365, IF([Date of Last Review]@row < [Hire Date]@row + 90, [Hire Date]@row + 184, IF(AND([PT/FT]@row = "Full Time", Position@row <> ""), [Date of Last Review]@row + 365, [Date of Last Review]@row + 184)))))

Note: The FT/PT and Position row is no longer needed. Instead I've pre-calculated everything to a column called "6 month eligible" that either has a yes or no instead of figuring if they are full or part time and if there is data in the position column.

All reviews need to be calculated relative to the initial hire date.

Review Criteria

Every newly hired employee gets a 60 day review

Every employee gets an annual review

Some employees gets a 6 month review

Examples using the above criteria:

Someone hired on 2/11/1997 would have reviews this year on 2/11/2024 (Annual) and 8/11/2024 (6 month) if they were eligible for a 6 month review.

Someone hired on 2/11/2024 would have reviews this year on 4/11/2024 (60 day) and 8/11/2024 (6 month) if they were eligible for a 6 month review.

Early reviews

For a review date to count towards/satisfy the review date, the review can be completed up to 2 months early (60 days before the next review date).

When to change the Next Review Date

Review dates should only iterate if:

The last review was completed up to 60 days before the next review date

The last review was overdue but was then completed after the next review date (completed late)

The review is still overdue and today is within 60 days of the date the next review date would have changed to had review had been completed on time (In other words, if the review is so overdue that we're now within 60 days of the next review, so we're just going to update it) ← If this adds too much complexity, this can be scrapped. I think there was a use case for it originally, but who knows if it even still exists.

Columns currently available and their description

Hire Date: Static Date of Hire

Date of Last Review: Manually entered via form

Next Review: This is the column where the formula will live

Employment Status: Lists Active for current employees

Tenure: The number of days since hire

Next Year's Work Anniversary: Effectively hire date plus 1 year

=IFERROR(IF([Employment Status]@row = "Active", DATE(YEAR(TODAY()) + 1, MONTH([Hire Date]@row), DAY([Hire Date]@row)), ""), "")

Other:

I've set an automation to refresh the sheet daily so the Today() function will update.

Below is a screen snip of the current sheet.

Help me Smartsheet community. You're my only hope.

bisaacs

Hey @Dan Y,

I'm trying to figure out exactly what isn't working in your formula. I know you mentioned that you aren't using the FT/PT criteria of the formula so I removed that and parsed the formula down to this:

=IF([Employee Status]@row <> "Active", "", IF([date of last review]@row = "", [Hire Date]@row + 60, IF([Eligible for 6 Month Review]@row = "Yes", [date of last review]@row + 184, [date of last review]@row + 365)))

As for your "When to Change the Next Review Date", it kind of looks like you're trying to introduce circular arguments, since you can't reference the cell that the formula is in. I'd maybe recommend using some conditional formatting to highlight if it meets some of the criteria you mentioned.

Hope this helps, and let me know what else I'm missing from your request!

Jgorsich

I think you are ultimately looking at a 2 sheet solution - one that tracks when reviews were done and one that summarizes into the sheet above.

For your tracker sheet, get the employee name, the date of the review, and a few helper columns in there. I'd make the first helper column be "Scheduled review" and have this pull (based on the employee name) from the summary sheet, then "ReviewOnTime" and have this be a comparison of the date of the review to when the review was scheduled and give a 1 if within +/-60 days (or whatever limit you want) and a 0 if outside of that. Then I'd make a "NextReviewDate" column that - if the review was performed on time, goes off of the schedule and if the review was late gets updated.

Then on your summary sheet you can have date of last review be a max(collect()) function where you pull all review dates for that employee and then choose the biggest and next review do the same but from the "NextReviewDate" column instead. You could also have a marker for "Last review was late" if you wanted (do an index match on the review date and check the "ReviewOnTime" column).

Trying to do it in a single sheet solution like you have is tricky, mainly because you might have multiple reviews in a row that are CLOSE to the wrong time, and then how do you adjust for them? If a person is hired and their first annual review is done at 11 months and their second annual review is done at 11 months and their third annual review is done at 11 months each review individually was done within your 60 day window but the cumulative effect was that the last was done 90 days early. With a single sheet solution, trying to keep track of that will be very complex - possibly something interesting using the mod function MIGHT work, but I'm betting against.

-Edit: For the mod function to facilitate a single sheet solution, and ignoring the 6 month windows, you might be able to do something like mod([date of last review]@row + 60 - [hire date]@row,365) to get an indicator of last review dates that are inside of a 60 day window of 365 days (acceptable results will be 0 to 120, anything larger than 120 will be outside of that window and therefore force a reschedule. Then, based on that result you can either add a year to the last review date (if the value is greater than 120) OR schedule out a year based on the hire date. That is a bit tricky, but it seems like "=(round(mod([date of last review]@row + 60 - [hire date]@row,365)/365,0)+1)*365 + [hire date]@row" should do the trick.

Obviously, you'll have to modify all of that for the 6 month windows, but just change the 365 values to 180 with an if statement and everything should continue to work.

Dan Y

@bisaacs

Your parsing looks good, but the problem still exists. The formula will add either 184 or 365 days to the date of last review. If the date of last review was done 1 month early for example, the new review date would be set to be due a month before the annual review is actually do the next time. Same goes for the 6 month. The formula I am trying to create would always set the date relative to the hire date.

Example: Employee Hire date is 1/1/24. THe 60 day review was completed, and since it is based on the Hire Date, the 6 month review would be still be 7/1/24, no matter what date it was completed. The Annual review would of course be 1/1/25. Let's say I decide I'm going to complete the review a little early, lets say on 6/1/24 which is within the allowed 60 day window. When I enter 6/1/24, the formula needs to change the next review date to 1/1/25, but currently, it would change it to 12/1/24 (or there about), 184 days after 6/1/24.

=IF([Employee Status]@row <> "Active", "", IF([date of last review]@row = "", [Hire Date]@row + 60, IF([Eligible for 6 Month Review]@row = "Yes", [date of last review]@row + 184, [date of last review]@row + 365)))

Does this make sense?

Dan Y

Thanks for the detailed response. I THINK I understand the approach, but I'll have to play with it.

One note, the last review date currently gets overwritten each time a new review is done, so comparing to multiple dates shouldn't be an issue. I like being able to keep all the review dates, though HR keeps that record as well so it isn't strictly needed. Does this information change your approach?