Skip to main content
Solved

How to set a date length based on certain dates

  • June 20, 2019
  • 6 replies
  • 61 views
J

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.

Best answer by kuovonne

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


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'
)

6 replies

JonathanBowen
Forum|alt.badge.img+18

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

J
W
Justin_Barrett
J

Amazing! Thank you so much!

J
  • Jacqui_Birchall
  • Author
  • New Participant
  • January 30, 2020

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


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
Forum|alt.badge.img+18

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


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

J
  • Jacqui_Birchall
  • Author
  • New Participant
  • February 25, 2020

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


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
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • February 25, 2020

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


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'
)
Terms & ConditionsAccessibility statement