0

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)

I have he excel formula which works perfectly:

=20+(EDATE(A2,24)<=DATE(YEAR(TODAY()),4,1))*4

I have been trying to apply the same principal on a calculated column in a SharePoint Library Where I had a Joining Date and a Leave column; I have twicked the code below but it does not work

=20+(DATE([Joining Date])<=DATE(YEAR(TODAY()),4,1))*4

Any ideas on how I can solve this?

1 Answer 1

0

You can try below and let me know if that meets your requirements

=IF(DATEDIF(JoiningDate,DATE(YEAR(TODAY()),4,1),"d")>=(365*2),24,20)

enter image description here

5
  • Apologies I had to abandon the project, and just restarting this now. I have used the formula but it appears not to work, example if I enter 20/03/19 it returns 24 as of today 13/02/21, it should return 20 as 2 years anniversary will be 01/04/21 Commented Feb 13, 2021 at 19:20
  • The Date on April 1 of the current year is 01/04/2021 and the joining date is 20/03/2019. Days between these two dates are 743 which is greater than 365x2=730 days, hence 24 days. If you're working with different logic, let me know Commented Feb 13, 2021 at 21:40
  • Apologies, the entitlement to 24 days only triggers if they have completed 2 full years by 1st April. Commented Feb 16, 2021 at 14:28
  • E.G. JoiningDate = 31/03/19 and Today(16/02/21) they will have 20 days however one reaching Today(01/04/21), they will have 24 days, however if your JoiningDate in 05/04/19 then on Today(01/04/21) they will still be on 20 days as they have not completed 2 full years by then, hope this makes sense Commented Feb 16, 2021 at 14:40
  • Then you will just need this =IF(DATEDIF(JoiningDate,TODAY(),"d")>=(365*2),24,20). For this kind of formula where TODAY() is involved, you need to run a workflow every day to calculate Days as Calculated columns don't get updated automatically Commented Feb 17, 2021 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.