Date cycle formula

[Freshman]

Hi all,

I need some help with a formula to calculate date cycles.
Each person have an employment date on which a 3 year cycle is based.
So at the end of their 3 years the cycle needs to update to the next 3 year cycle.

For example
Person Employment date Cycle Start
1 01 Jan 2015 01 Jan 2015
2 01 Jan 2006 01 Jan 2012

In the case of Person 2 above the cycle would have started at 2006 - 2009 and then moved to 2009 - 2012 and then 2012 - 2015 and now 2015 - 2018

I have the following formula at the moment but need to make 100% I'm not missing something.
Also - would it be fine to run the update query each time for all persons or must it be only for the ones where the current date does not fall within the current 3 year cycle?

CycleStart = DateAdd("yyyy",Int(DateDiff("d",[EmpDate],Date())/365/3)*3,[EmpDate])

Would value your input on another way to do this as well.

Thanks
Pierre

 

[plog]

You don't store calculated values, so this doesn't belong in a table, it belongs in a query. Then when you need to reference their cycle start, you use the query. Your formula looks good, just not the way you intend to use it.

 

[CJ_London]

your examples don't go far enough to explain the background

Are you saying the 3 year cycle starts at the same time for everyone

or always starts on the 1st Jan in the year of employment

or starts on the day of employment for each person

 

[Freshman]

your examples don't go far enough to explain the background

Are you saying the 3 year cycle starts at the same time for everyone

or always starts on the 1st Jan in the year of employment

or starts on the day of employment for each person

Sorry if I was unclear: Day of emp of each person