Pension Date

[keno0177]

How to Calculate Pension Date from Date of Birth?

@Suvrutt_Gurjar

[dbaum]

Date and time expressions - AppSheet Help 

[keno0177]

Thanks, but it's not helping. It's not like excel expressions, there is no many expressions in AppSheet like Sheet or Excel

[Sirfyaad]

For example, if the date of birth is in cell A2, you can use the following formula in another cell to calculate the date 65 years later: =DATE(YEAR(A2) + 65, MONTH(A2), DAY(A2))

---

@keno0177 wrote:

excel expressions

---

[ZOCO_Unlimited]

Adjust for column names

FLOOR(HOUR([Pension Date]-[Date of Birth])/365/24)&" yr "&
FLOOR(MOD((HOUR([Pension Date]-[Date of Birth])/24),365)*12/365) & " mo"

Will result in years and months. example: 23 yr 5 mo 

[Suvrutt_Gurjar]

@keno0177 : Welcome to the community. One request to you will be to write a sufficiently elaborate requirement for the community to respond in a proper manner. May I say , your current requirement is too short.

I would put my requirement something like :

"How to calculate the pension date from the date of birth? So if date of birth is 12/15/2000 , the pension date will be 62 years from the date of birth, that is 12/15/2062"

This will let the community know A) I am using mm/dd/yyyy  date system B) The pension date is after 62 years of age in the geography I am referring C) And a specific example conveys what kind of addition I am looking for. Sometimes, pension date is rounded up to the nearest month end.

Now, if understanding of your requirement is correct, you could use the EOMONTH() function.

Assuming pension date is at the completion of 65 years of age, you could use an expression something like

EOMONTH( [Date of Birth], 779) +DAY( [Date of Birth])

Due to peculiarity of calendar which has leap years, the above expression will give the date of pension as 1st March for a person born on 29th February in a leap year, because there is simply no 29th Feb in a non leap year and by adding 65 years to a leap year, a person born in leap year will always have pension date in a non leap year.

EOMONTH() - AppSheet Help

Finally I may also request you to post a query without specifically referring to any community member. Anybody in this community is capable of answering queries. You will get the best possible and fastest possible response because community responds 24 X 7 but individual members log in limited hours as per their time zones.  All the best with your app making.

[Sirfyaad]

1. Create a virtual column in table to hold the calculated pension date. Let's assume the column name is "Pension Date."

2. In the "Pension Date" virtual column, click on the column's formula editor.

3. Use the following formula to calculate the pension date:

DATE(
  YEAR([Date of Birth]) + 65,
  MONTH([Date of Birth]),
  DAY([Date of Birth])
)

Make sure to replace [Date of Birth] with the actual column name that holds the date of birth value in your table.

[Suvrutt_Gurjar]

@keno0177 ,

Please note that the expression 

DATE(
  YEAR([Date of Birth]) + 65,
  MONTH([Date of Birth]),
  DAY([Date of Birth])
)

will not work if the [Date of Birth] is 29th Feb in a leap year. It will show a date of 29th Feb after 65 years in a non leap year. A non leap year does not have a date of 29th Feb.

Also I am afraid, this expression is syntactically wrong. It does not have the "/"  or "-" expected in a date format. so it will give error in the expression assistant.