# how to decide the completed years(Length of service)

Options
edited May 2023 in Modeling

hi guys~

In Sales_Rep_Details module, What we know is the sale start date, so I calculate the completed years by:

Start_Date_Current_Period(2020-01-01) - Start Date

then I devide it by 356

the final formula is

ROUND(('SYS01 Time Settings by Month'.Start_Date_Current_Period - Start Date) / 365, 0, Up,NORMAL)

by compare my module with the given sample, They seem all the same, but,

it turns wrong

SO, I assume It is the problem of number of completed years.

would you please tell me the the right way to calculate it?

or, May be the issue of Role or Tenure?

It is best of giving same sample capture of it

Is my module right?

thank you

———————————————————Update 2023/5/6——————————I change formula of Completed Years to

ROUND(('SYS01 Time Settings by Month'.Start_Date_Current_Period - Start Date) / 365, 0, NEAREST, NORMAL) than it matched.

but, tenure bacome unmatch.

but again, I think the sample is wrong, if current period is 2020 Jan, 2020/10/1 should be not start.

• edited May 2023 Answer ✓
Options

Hi @Livy ,

Yes, a Value less than or equal to 0 should be "not started?".
Check your tenure line-item formula once.

Attaching a screenshot for your reference.

As per your screenshot even for 0 it is <1 yr and check correct it for 0 it should be Not started?.

Hope this helps :).

Regards,
Venkatesh

• Options

Hi @Livy ,

The formula for completed years will be like below:

YEAR(CURRENTPERIODSTART()) - YEAR(Start Date).

Hope this helps :)

Regards,

Venkatesh

• Options

thank you @Venki9902

but how about the situation like this

YEAR(CURRENTPERIODSTART())=YEAR(2020/01/01)=2020

YEAR(Start Date)=YEAR(2020/10/31)=2020

2020-2020=0

but it should be "not started"

• Options

I agree with @Livy@acn. The formula YEAR(CURRENTPERIODSTART()) - YEAR(Start Date). will incorrectly say employees have not started if the start data is in the same year as current period.