Date Calculation

[mtagliaferri]

I am trying to calculate leave entitlement based on the date of joining and current date, at antry date the person has 20 days of leave if the person has completed their 2nd year anniversary by 01 April then they would get 4 extra days.
E.G.

• if joining date is 15/03/18 annual leave = 24 (completed 2 years by 01 April of current year 2020)
• if joining date is 15/06/18 annual leave = 20 (has not completed 2 full yeas by 01 April of current year 2020)

Any suggestions?
Thanks

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B
1
2	15/03/2018	24
3	15/06/2018	20
4	01/04/2018	20
Main

Cell Formulas
Range	Formula
B2:B4	B2	=20+(EDATE(A2,24)<DATE(YEAR(TODAY()),4,1))*4

You may need to change the < to <= depending what should happen if they start on the 1st of April

 

[mtagliaferri]

How about

+Fluff v2.xlsm
	A	B
1
2	15/03/2018	24
3	15/06/2018	20
4	01/04/2018	20
Main

Cell Formulas
Range	Formula
B2:B4	B2	=20+(EDATE(A2,24)<DATE(YEAR(TODAY()),4,1))*4

You may need to change the < to <= depending what should happen if they start on the 1st of April

Thanks that is spot on!!

 

[Fluff]

You're welcome & thanks for the feedback.