Total Service Years (Year & Month)

[desmond_lee]

Hi Guys

I always have trouble in date calculation. Let's an employee join a compay June 1, 2022. The total service years until the currnet date, today(), August 8, 2023 is

1. June 1 , 2022 to June 1, 2023 -  1 year

2. July 1, 2023 to July 31,2023 - 1 month

3. August 1, 2023 to August 8, 2023 - 8 days

Hence I year, 1 month, 8 days

How to put this into expresssion.

[SkrOYC · Accepted solution]

Check this:

Convert Birthdate to Current Age in Years/Months f... - Google Cloud Community

The main issue is that there is no way to make an 100% accurate version of this, but it gets pretty close

[Suvrutt_Gurjar]

In what format you are looking for the output?

---

@desmond_lee wrote:

Hence I year, 1 month, 8 days

---

As above or just in days such as 403 days

[desmond_lee]

In this format : 1  YEAR 1 MONTH 8 DAY. I wonder is it possible to put (S) behind for >2 like 8 DAYS. 

[Suvrutt_Gurjar]

Oh okay. I think based on currently available functions in AppSheet, it will be a lengthy expression.

I will post back if there is a decently compact expression. 

Maybe some other community member will also have a better way of calculating this.

[desmond_lee]

I create this expresssion but there is a minus there. Don't know where I done wrong. [SERVICE YEAR]&" YEAR(S) "&[SERVICE MONTH]&" MONTH(S)"

YEAR

(((YEAR(TODAY()) - YEAR([DATE JOIN])) * 12))/12

Month

((((YEAR(TODAY()) - YEAR([DATE JOIN])) * 12) + MONTH(TODAY())) - MONTH([DATE JOIN]))-(((YEAR(TODAY()) - YEAR([DATE JOIN])) * 12))

[desmond_lee]

Looks like no one able to resolve this

[Suvrutt_Gurjar]

As mentioned earlier, it will be a complex expression and not a straightforward based on existing AppSheet functions, especially if you are looking for precise number of months and days.

[desmond_lee]

OK noted. Consider this close. Thanks anywhere

[SkrOYC · Accepted solution]

Check this:

Convert Birthdate to Current Age in Years/Months f... - Google Cloud Community

The main issue is that there is no way to make an 100% accurate version of this, but it gets pretty close

[desmond_lee]

thank you. Very good discussion

[Suvrutt_Gurjar · Accepted solution]

Yes, as @SkrOYC also mentioned , the nice tip shared by him will give result in years and months and not days. Also it considers number of days in a year as 365. Every 4 years leap year is there with 366 days. So any calculation around leap year even if calculated for 1 or 2 years will add an error. Also months have 28, 29, 30 or 31 days. The expression in the tip considers each month of 365/12 that is 30.41 days. 

So as @SkrOYC also rightly guided it will be an approximate calculation.

From your question it sounded that you are calculating it for a service tenure and and as such needs to be precise and hence we( @SkrOYC  and I) mentioned on approximation with available technique and need of a complex expression for precise results,

[desmond_lee]

Thank you. I take this is acceptable FLOOR(HOUR(TODAY()-[DATE JOIN])/365.25/24)&" YEAR "&
FLOOR(MOD((HOUR(TODAY()-[DATE JOIN])/24),365.25)*12/365) & " MONTH"

[Suvrutt_Gurjar]

Excellent. Thank you for the update. Good to know you got a solution that works for you.

[SkrOYC]

I actually suggest you to check the whole thread since @Steve added a neat expression to get a more accurate Days portion. That's also why I added a link to the OP

[desmond_lee]

Yes I saw that. Thanks