How to set a date length based on certain dates

Hi all,

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.

Hi @Jacqui_Birchall - I think this gives you what you want:

The formula is:

DATETIME_DIFF(DATETIME_PARSE('30/4/' & DATETIME_FORMAT(TODAY(), 'YYYY'), 'DD/M/YYYY'), Started, 'years')

It compares the “started” date with 30th April THIS YEAR (result shown in whole years)

JB

3 Likes

Amazing! Thank you so much!