How to set a date length based on certain dates

[Jacqui_Birchall]

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.

[JonathanBowen]

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

[Jacqui_Birchall]

Amazing! Thank you so much!

[Jacqui_Birchall]

Hey,

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?

Thank you so much in advance!
Jacqui

[JonathanBowen]

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:

DATETIME_DIFF(TODAY(), Started, 'years')

JB

[Jacqui_Birchall]

Hey JB,

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.

Cheers,
Jacqui

[kuovonne · Accepted solution]

The previous formula uses the 30th of April in the current year as the end date in the calculation. You want the end date to be TODAY.

Here is a formula that uses the 30th of April as the start date for the calculation.

• If the actual start date is in April or earlier, calculate years from the 30th of April of the same year.
• If the actual start date is in May or later, calculate years from the 30th of April of the following year.

DATETIME_DIFF(TODAY(), 
  IF(MONTH({Start}) <= 4, 
    DATETIME_PARSE("30/04/" & YEAR({Start}), 'DD/MM/YYYY'),
    DATETIME_PARSE("30/04/" & (YEAR({Start}) + 1), 'DD/MM/YYYY')
  ),
  'years'
)