Using Formula column as input for date-based views

I am currently using a ‘People’ board to track various details about individual team members across the org. This board includes which team they’re on, their start date, birthday, location, etc. Our HRMS includes all employee information, but we needed a place to track contractor info as well so we use Monday for that.

Using the Formula Column to set Performance Review Dates

We have an annual and mid-year performance review schedule for employees (depending on role) for which I have set up a formula column to help us determine the date of their next review. This is the formula step-by-step:

// Get the number of days since the team member started

ROUNDDOWN(DAYS(TODAY(),{Start Date}),0)

// Divide by 365.25 (a year) to get the number of reviews they should have had to this point.

ROUNDDOWN(DIVIDE(ROUNDDOWN(DAYS(TODAY(),{Start Date}),0),365.25),0)

// Add 1 to get the number of their next review

ROUNDDOWN(DIVIDE(ROUNDDOWN(DAYS(TODAY(),{Start Date}),0),365.25),0)+1

// Multiply by 365.25 to get the number of days from their start date of that review.

MULTIPLY(ROUNDDOWN(DIVIDE(ROUNDDOWN(DAYS(TODAY(),{Start Date}),0),365.25),0)+1,365.25)

// Return the date

ADD_DAYS({Start Date},MULTIPLY(ROUNDDOWN(DIVIDE(ROUNDDOWN(DAYS(TODAY(),{Start Date}),0),365.25),0)+1,365.25))

// Format the date

FORMAT_DATE(ADD_DAYS({Start Date},MULTIPLY(ROUNDDOWN(DIVIDE(ROUNDDOWN(DAYS(TODAY(),{Start Date}),0),365.25),0)+1,365.25)), "MMM DD, YYYY")

To get the mid-year review we use a separate column to subtract 182 and if that date has already passed we add 182 to the next annual instead.

Next Steps - Need Input

Ideally I wanted to create one calendar view that we can then share with different leaders across the org to see their teams review calendar so they can plan accordingly. Unfortunately the formula column isn’t recognized as a datetime column in order to create a calendar view. I’m wondering if anyone has suggestions on next steps or how I can improve on this to make it more efficient or functional.

As of now the plan is to download an export of the board and drop it into a pre-defined Google Sheet which will then pull different departments into different tabs and sort based on review dates. We’ll do this quarterly to give leaders the most recent schedule of reviews coming up.

image