Conditional Date Calculation. . .

[Sebastien Bergeron]

Here you go

I have a calculation that give me the "Period of employment" of each staff member using the current date and their respective start date. The result is displayed like this : 1 Year , 3 Months , 10 Days.

Now, I want to calculate their " Accumulated Vacation during their POE". The thing is . . . for the first 4 years, they get 10 Vacation days per year. After 5 years they get 15 days of vacation per year.

What do I have to add so my formula calculate the first 4 years @ 10 days per years and then 15 days per year for the subsequent ?

Also, I want my formula to add the Vacation days every months. . .

Here what I got so far:

GetAsNumber(Year(Current_Date) -

Year(Start_Date) - Case(Current_Date <

Date(Month(Start_Date); Day(Start_Date); Year(Current_Date)); 1; 0))*10 +

GetAsNumber(Mod(Month(Current_Date) -

Month(Start_Date) + 12 - Case(Day(Current_Date) <

Day(Start_Date); 1; 0); 12))* (10/12)

[comment]

Try something like:

Let ( [

today = Get (CurrentDate) ;

years = Year ( today ) - Year ( Start_Date ) - ( today < Date ( Month ( Start_Date ) ; Day ( Start_Date ) ; Year ( today ) ) ) 

] ;

10 * Min ( years ; 4 ) + 15 * Max ( 0 ; years - 4 )

)

[Sebastien Bergeron]

Thanks a lot !

It does look like it's working great for years, I need to add the month calculation after. Which is the same rule but applied to months while taking in consideration the amount of years.

So (10/12) for each month or (15/12) for each month after 4 years.

[comment]

I haven't tested this, but I believe it would be a simple adjustment:

Let ( [

today = Get (CurrentDate) ;

months = 12 * Year ( today ) + Month ( today ) - 12 * Year ( Start_Date ) - Month ( Start_Date ) - ( today < Date ( Month ( today ) ; Day ( Start_Date ) ; Year ( today ) ) ) 

] ;

10/12 * Min ( months ; 48 ) + 15/12 * Max ( 0 ; months - 48 )

)

It seems a bit unfair, though: employees that started working in February accumulate their vacation earlier than those that started in January, for example.

[Sebastien Bergeron]

The formula seem to work great ! Thanks a lot again for your kind help. In my original one I was counting the months separately, but I don't think it make much difference . . .

I don't get your observation about the idea that employee that start working in February accumulate their vacation earlier than in January. Are you saying that because it's now Month based instead of yearly based ?

Also I would like to add a field that trigger the calculation to be done at 10 days for the first 4 years like in your formula (that would be the (option 1) or straight at 15 days per year (option 2)

What do you recommend ?

[comment]

Are you saying that because it's now Month based instead of yearly based ?

Well yes - if you started on January 1, you'll get your first 10/12 days of vacation after working for 31 days; your co-worker that started a month later gets the same after 28 days. It evens out eventually.

I am not sure what you use this for and why do you need "to add a field that trigger the calculation". If the calculation is set to unstored, it will be correct at all times.

[Sebastien Bergeron]

The field Option1 or Option2 would be to define if the employee gets 10 days per year of vacation or 15 days from day one of their start date . In some cases, some senior employee come in with their accumulated benefit from other job ! :(