I am formatting my database to show how long a volunteer has been with us (in order to determine when to give them their 1 year of service, 2 years of service etc), however, the cut-off date each year for our volunteers is 30th April.
For example, if a volunteer starts with us on 1/5/2018, at 1/5/2019 the years of service is 0, but at 1/5/2020 it will be 1 year.
If a volunteer started with us on 29/4/2018, at 1/5/2019 the years of service is 1, and at 1/5/2020 it will be 2 years.
I hope this makes sense!
I’d love to know how to formulate this so I can get a ‘1’ or ‘2’ etc. Thanks in advance for your assistance.
Thanks so much for your help with the above formula. I really appreciate it.
Using the formula, I have noticed that in some cases the dates are rounding up incorrectly, e.g: |Date of Registration |Volunteering Length |Years of Service using 30/04|
|20/04/2018 |1 years, 9 months |2|
|24/04/2016 |3 years, 9 months |4|
In these examples the years of service should be 1year less.
Is there a way to amend the formula to make this show correctly?
Hi @Jacqui_Birchall - I think the formula is calculating correctly (although it may not be the correct formula for your scenario). It is always comparing the start date to the 30th April of the “current year”, so as of today it is comparing the start date to 30/04/2020, which is, of course, in the future. But the number of whole years between 20/04/2018 and 30/04/2020 is 2, so the formula is correct.
The field showing “1 year, 9 months” is, I’m guessing, service from start to “today”. If you want to see the number of whole years to “today” then the original formula could be modified to:
Thanks again for your response! Greatly appreciate it.
Unfortunately it’s a bit of a tricky scenario as my workplace uses the 30th April as the date for measuring years of service (I’m unsure why this is the case but unfortunately it can’t be changed).
For example, if a volunteer starts with us on 1/5/2018, at 1/5/2019 the years of service is 0, but at 1/5/2020 it will be 1 year of service.
And if a volunteer started with us on 29/4/2018, at 1/5/2019 the years of service is 1, and at 1/5/2020 it will be 2 years of service.
So ideally I am looking for a formula that will show the years of service in years, as of today, so when we are giving volunteer awards we have the correct time showing.
I’m sorry to be so difficult!! Thank you so much in advance.