Get the most recent start date following 5 year intervals

[JulieLamberth]

Hi there.  This may seem like a strange query, however I have some data with employees' start dates and I need to find out the beginning of the most recent 5-year interval since each person commenced and when the next 5-year interval begins.  These will be different for each person, depending on their date commenced.

Below is an example of the type of data I currently have (columns 1 and 2), my own calculations (column 3 - not required as output for my purposes) and what I would like to see in columns 4 and 5)  I also have a column with DateTime.LocalNow() to reference the current date:

Employee	Date Commenced	All 5-Year Intervals Commenced	Last 5-Year Interval Commenced	Next 5 Year Interval Commences
12345	17/09/1992	17/9/97,17/9/02, 17/9/07, 17/9/12, 17/9/17, 17/9/22	17/09/2022	17/09/2027
23456	1/05/2014	1/5/19, 1/5/24	1/05/2024	1/05/2029

Is anyone able to point me in the right direction of how to get columns 4 and 5 please?  If so I would be eternally grateful.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Last 5 year interval commenced = EDATE(Data[Date Commenced],120*int(DIVIDE(DATEDIFF(Data[Date Commenced],today(),MONTH),120)))

Next 5 year interval commences = EDATE([Last 5 year interval commenced],60)

Hope this helps.

[JulieLamberth]

Thank you so much.  That worked brilliantly and is scaleable.  This community is amazing.

[Ashish_Mathur]

You are welcome.

[rajendraongole1]

Hi @JulieLamberth - Create a calculate the most recent 5-year interval prior to today, based on the employee's start date

Last 5-Year Interval Commenced =
VAR StartDate = [Date Commenced]
VAR YearsSinceStart = DATEDIFF(StartDate, TODAY(), YEAR)
VAR LastIntervalYears = INT(YearsSinceStart / 5) * 5
RETURN
DATE(YEAR(StartDate) + LastIntervalYears, MONTH(StartDate), DAY(StartDate))

create another calculated column will calculate the next 5-year interval that begins after today, based on the employee's start date

Next 5-Year Interval Commences =
VAR StartDate = [Date Commenced]
VAR YearsSinceStart = DATEDIFF(StartDate, TODAY(), YEAR)
VAR NextIntervalYears = (INT(YearsSinceStart / 5) + 1) * 5
RETURN
DATE(YEAR(StartDate) + NextIntervalYears, MONTH(StartDate), DAY(StartDate))

Hope this helps.

[JulieLamberth]

Thank you for such a prompt reply.  However I am having problems - probably because I'm doing it wrong.  I used the Custom Column function on the Add Column menu, copied your formula into it but am getting an error on the first line.  Am I trying to put this formula in the wrong place?