Year Month Calculation on TODAY ()

[robhel]

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

[Ashish_Mathur]

Hi,

Please explain how the answer is 0.6, 0.4, 4.10, 9.10 etc?

[robhel]

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

Hope this answer your question

[v-chuncz-msft · Accepted solution]

@robhel ,

Try to change it as follows.

    CONCATENATE (
        CONCATENATE ( Years, "." ),
        MOD ( Months - ( Years * 12 ) + 12, 12 )
    )