cancel
Showing results for
Did you mean:
Regular Visitor

Year Month Calculation on TODAY ()

Hello Guru's

Looking for your expert advice on how to achieve the following scenario of calculating the number of Year’s & Months from TODAY() until next lot of Long Service Leave (LSL) will be available.

There are 2 parts to this scenario:

LSL is available after 10years from commencement date

Additional 9days LSL for every year after 10yrs based on commencement date

I’ve came up with an extremely hack work-around for both steps above, which is still not providing me with required results:

Step 1)

From my table “CurrentEmployee”, I’ve split [Commencement Date] to DDMM and then included 2019 to it

I created a second table for employee’s that are under 10years, adding [Year] + 10 to commencement date

Then merged the 2 fields together providing existing LSL & upcoming LSL

Step 2)

I applied a new column with the following:

DaysLSL =

VAR Months = DATEDIFF(TODAY(),[MergedField],MONTH)

VAR Years = ROUNDDOWN(Months/12,0)

RETURN CONCATENATE(CONCATENATE(Years, “.”),Months-(Years*12)))

Which gives me the desired result, unless the date is prior to TODAY (re YMResult)

Greatly appreciate any assistance on a way to get desired result

 EmpID EmpName CommDate MergedField YMResult YMDesiredResult 123 Rod 15/10/2007 15/10/2019 0.6 0.6 124 Jim 2/08/1999 2/08/2019 0.4 0.4 125 Barney 5/02/2014 5/02/2024 4.10 4.10 126 Carl 18/02/2019 18/02/2029 9.10 9.10 127 Hannah 1/03/2008 1/03/2019 0.-1 .11 128 Mary 9/12/1999 9/12/2019 0.8 0.8 129 Wayne 27/06/1983 27/06/2019 0.2 0.2 130 Kelly 8/01/1985 8/01/2019 0.-3 .9 131 Kirsty 1/02/1983 1/02/2019 0.-2 .10 132 Emma 4/03/2013 4/03/2023 3.11 3.11 133 Lou 18/10/1999 18/10/2019 0.6 0.6 134 Sarah 16/01/2006 16/01/2019 0.-3 .9 135 Mike 22/09/2008 22/09/2019 0.5 0.5 136 Fred 15/04/1999 15/04/2019 0.0 0.0
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: Year Month Calculation on TODAY ()

Try to change it as follows.

```    CONCATENATE (
CONCATENATE ( Years, "." ),
MOD ( Months - ( Years * 12 ) + 12, 12 )
)```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

Re: Year Month Calculation on TODAY ()

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

Re: Year Month Calculation on TODAY ()

Thanks for response & in answer to your question, the result is based on TODAY less Commncement MMYY = number of months or years until next allocation of LSL, example

TODAY 27/04/2019 - 12/01/2022 Commencement Date (= 12/01/2012 + 10y) = 2years & 9Mths until LSL

TODAY 27/04/2019 - 29/10/2019 Commencement DDMM & Current Year (29/10/1979 to 29/10/2019) = .6 mths until next LSL days

Community Support Team

Re: Year Month Calculation on TODAY ()

Try to change it as follows.

```    CONCATENATE (
CONCATENATE ( Years, "." ),
MOD ( Months - ( Years * 12 ) + 12, 12 )
)```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements