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
June 1 , 2022 to June 1, 2023 - 1 year
July 1, 2023 to July 31,2023 - 1 month
August 1, 2023 to August 8, 2023 - 8 days
Hence I year, 1 month, 8 days
How to put this into expresssion.
In what format you are looking for the output?
As above or just in days such as 403 days
In this format : 1 YEAR 1 MONTH 8 DAY. I wonder is it possible to put (S) behind for >2 like 8 DAYS.
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.
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))
Looks like no one able to resolve this
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.
OK noted. Consider this close. Thanks anywhere
Check this:
The main issue is that there is no way to make an 100% accurate version of this, but it gets pretty close
thank you. Very good discussion
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,
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”
Excellent. Thank you for the update. Good to know you got a solution that works for you.
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
Yes I saw that. Thanks
