Convert Birthdate to Current Age in Years/Months format

GreenFlux · November 15, 2019, 1:56pm

Calculating the current Age based on a date-of-birth column:

Formula
FLOOR(HOUR(TODAY()-[DOB])/365/24)&" yr "&
FLOOR(MOD((HOUR(TODAY()-[DOB])/24),365)*12/365) & " mo"

Line 1
HOUR(TODAY()-[DOB]) returns the number of Hours in the duration
/365/24 converts hours to years, and FLOOR gives the number of full years, dropping the decimal.

Line 2
(HOUR(TODAY()-[DOB])/24) returns the number of days in the duration
MOD((...),365) finds the remainder in days, after removing the full-years
FLOOR(MOD(...)*12/365) converts those remaining days to months, dropping the decimal.

This could also be used to show other durations, such as a count down to a future date.

Fernando_Lopez1 · November 15, 2019, 2:15pm

Excelent, Thank you !!!

Fabian_Weller · November 15, 2019, 7:59pm

Thank you very much for sharing.

MultiTech · February 18, 2020, 4:27pm

GreenFlux:

FLOOR(HOUR(TODAY()-[DOB])/365/24)&" yr "&> FLOOR(MOD((HOUR(TODAY()-[DOB])/24),365)*12/365) & " mo"

You da man! Just a simple search away and all this awesomeness is done for me!

MultiTech · June 7, 2020, 10:14pm

Bout the 14th time I’ve come back to this post… can’t thank you enough!

GreenFlux · June 7, 2020, 10:25pm

Ha! Thanks. It was tricky to figure out, but kind of a fun challenge too.

I’m glad others can make use of it. I spent a few hours scratching my head on this one! The more it gets re-used, the more it was worth it.

MultiTech · June 7, 2020, 10:27pm

says it all.

gioKelly · November 2, 2020, 2:28pm

nice tip! Do you happen to know how can I get also the days?
Thanks in advance for the help!

GreenFlux · November 3, 2020, 12:33pm

This is pretty close.

FLOOR(HOUR(TODAY()-[DOB])/365/24)&" yr "& 
FLOOR(MOD((HOUR(TODAY()-[DOB])/24),365)*12/365) & " mo " & 

IF(
DAY(TODAY())>=DAY([DOB]),
DAY(TODAY())-DAY([DOB]),
DAY(TODAY())+FLOOR(HOUR(EOMONTH([DOB],0)-[DOB])/24)
  ) & " days"

I tried the MOD() method, but the numbers always seemed off because of months having different number of days. So I went with the DAY() -number of TODAY() compared to the DAY([DOB]).

I haven’t fully tested it yet. You might see something like X yrs X mo 32 days or -1 days, depending on how the dates line up.

anonymous · February 15, 2021, 11:36pm

This seems to be working well for me for calculating the days portion:

if(((hour(today()-date(concatenate((month(EOMONTH(TODAY(), -1))),“/”,(day([DOB])),“/”,(year(EOMONTH(TODAY(), -1)))))))/24)<(hour(today()-(EOMONTH(TODAY(), -2) + DAY(TODAY())))/24),((hour(today()-date(concatenate((month(EOMONTH(TODAY(), -1))),“/”,(day([DOB])),“/”,(year(EOMONTH(TODAY(), -1)))))))/24),(hour(today()-date(concatenate(month(TODAY()),“/”,day([DOB]),“/”,year(TODAY()))))/24))
& " days"

Steve · February 16, 2021, 2:18pm

Here’s your expression with some reformatting and a lot fewer parentheses:

CONCATENATE(
  IF(
    (
      (
        HOUR(
          TODAY()
          - DATE(
            CONCATENATE(
              MONTH(EOMONTH(TODAY(), -1)),
              "/",
              DAY([DOB]),
              "/",
              YEAR(EOMONTH(TODAY(), -1))
            )
          )
        )
        / 24
      )
      < (
        HOUR(
          TODAY()
          - (
            EOMONTH(TODAY(), -2)
            + DAY(TODAY())
          )
        )
        / 24
      )
    ),
    (
      HOUR(
        TODAY()
        - DATE(
          CONCATENATE(
            MONTH(EOMONTH(TODAY(), -1)),
            "/",
            DAY([DOB]),
            "/",
            YEAR(EOMONTH(TODAY(), -1))
          )
        )
      )
      / 24
    ),
    (
      HOUR(
        TODAY()
        - DATE(
          CONCATENATE(
            MONTH(TODAY()),
            "/",
            DAY([DOB]),
            "/",
            YEAR(TODAY())
          )
        )
      )
      / 24
    )
  ),
  " days"
)

Steve · February 16, 2021, 2:27pm

And here it is with EOMONTH() rather than DATE(CONCATENATE(...)):

CONCATENATE(
  IF(
    (
      (
        HOUR(
          TODAY()
          - (
            EOMONTH(TODAY(), -2)
            + DAY([DOB])
          )
        )
        / 24
      )
      < (
        HOUR(
          TODAY()
          - (
            EOMONTH(TODAY(), -2)
            + DAY(TODAY())
          )
        )
        / 24
      )
    ),
    (
      HOUR(
        TODAY()
        - (
          EOMONTH(TODAY(), -2)
          + DAY([DOB])
        )
      )
      / 24
    ),
    (
      HOUR(
        TODAY()
        - (
          EOMONTH(TODAY(), -1)
          + DAY([DOB])
        )
      )
      / 24
    )
  ),
  " days"
)

anonymous · February 17, 2021, 2:41am

Lol yes, I am not neat with my formulas and usually just keep throwing stuff in there til they work. Thanks for cleaning that up!

I also came up with the formula below that ensures that the month number is dependent on the anniversary day of the month. The one above would occasionally calculate the wrong month as you approached the anniversary day.

if(day(today()) >= day([DOB]),
if(and(year(today())=year([DOB]),month(today())=month([DOB])),0,
if(month(today())<month([DOB]),(month(today())-month([DOB]))+12,month(today())-month([DOB]))),
if(and(year(today())>year([DOB]),month(today())=month([DOB])),11,
if(month(today())<month([DOB]),(month(today())-month([DOB]))+11,(month(today())-month([DOB]))-1)))
& " mo"

Steve · February 17, 2021, 2:37pm

Well done!

anonymous · March 24, 2021, 1:21pm

That formula really helped me out. Thank you.

A suggested tweak would be to change 365 to 365.25. When I did that it gave me a more accurate result.

anonymous · May 6, 2021, 9:11am

I have a column with Date (DD/MM/YYYY) datatype in which I want to select the only MM/YYYY and the date should be fixed like the 1st date of every month.
So is this possible in the AppSheet?

Steve · May 6, 2021, 1:21pm

sonam:

I want to select the only MM/YYYY

I don’t understand what you mean by “select the only”. Please clarify.

anonymous · May 6, 2021, 1:41pm

When I am selecting a date in my column I only want the month and year to change. The day should be fixed i.e 1st of every month.

Shashwat_Hemato · May 26, 2021, 4:14am

Thanks

anonymous · October 19, 2021, 6:29am

I is very helpful thank you so much !
I used that formula to calculate due date but it is having error in calculating days

IFS(
  ISBLANK([Due Date]),
    "Unscheduled",
  (TODAY() > [Due Date]),
    "Overdue!",
  (TODAY() = [Due Date]),
    "Due today",
  (TODAY() = ([Due Date] - 1)),
    "Due tomorrow",
  TRUE,
    (FLOOR(HOUR([Due Date]-today())/365/24)&" yr "&
FLOOR(MOD((HOUR([Due Date]-today())/24),365)*12/365) & " mo" &
FLOOR(HOUR(EOMONTH([Due Date],0)-[Due Date])/24)
  ) & " days"

)

for example for today on 19th oct 21 and due date 20 jan 22 it is calculating 3 month 11 days instead of 3 months 1 day, I think i made some error in last step can anybody figure it out and help ! thanks in advance.

Topic		Replies	Views
Convert Birthdate to Current Age in Years/Months/Days format AppSheet Q&A expressions	3	16	May 30, 2024
expression to auto calculate current age ?... AppSheet Q&A expressions	25	20	March 21, 2022
Exact age only in years AppSheet Q&A expressions	1	12	August 18, 2021

Convert Birthdate to Current Age in Years/Months format

AI Suggested topics