Convert Birthdate to Current Age in Years/Months f...

GreenFlux · 11-15-2019 05:56 AM

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

Excelent, Thank you !!!

Fabian_Weller

Thank you very much for sharing.

MultiTech

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

MultiTech

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

GreenFlux

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

says it all.

gioKelly

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

GreenFlux

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.

MyYetti

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

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

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"
)

MyYetti

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

Well done!

notmat111

Hi Steve,

Thanks so much for this. Is it possible for you to help me amend this expression? I need it to compare against another column ([Service Date (Procedure)]) instead of TODAY() values.

Please could you kindly assist? I've tried correcting it myself but it gives an off result.

Thank you.

Allen_Jacques

I could for sure help you amend the formula and a lot more... please send
me a private email at <PII removed by staff> and we can meet to discuss.

Regards,

AJax-Allen Jacques
<PII removed by staff>

Simon_Zammit

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.

sonam2

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

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

sonam2

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

Thanks

naresh_chauhan1

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.

Martin_Hurtado

Hello … Could you solve it? Excellent way of working …

MultiTech

IFS(
  ISBLANK([Due Date]),
    "Unscheduled",
  TODAY() > [Due Date],
    "Overdue!",
  TODAY() = [Due Date],
    "Due today",
  TODAY() = [Due Date] - 1,
    "Due tomorrow",
  TRUE,
	Concatenate(
		FLOOR( (TOTALHOURS([Due Date] - today()) / 24) / 365 ),
		" yr",
		ifs(FLOOR( (TOTALHOURS([Due Date] - today()) / 24) / 365 ) > 1,
			"s"
		),
		" ", 
		ifs(
			month([Due Date]) = month(today()),
				0,
			month([Due Date]) > month(today()),
				(12 - month([Due Date])) + month(today()),
			month([Due Date]) < month(today()),
				month([Due Date]) - month(today())
		),
		" mo ",
		ifs(
		  day([Due Date]) = day(today()),
			0,
		  day([Due Date]) > day(today()),
			day([Due Date]) - day(today()),
		  day([Due Date]) < day(today()),
			(EOMONTH(today(), 0) - today()) + day([Due Date])
		),
		" days"
	)
)

As I see it, you have to change operations based on where TODAY() sits in relation to the [Due Date] depending on which number you’re trying to find (months or days).

Martin_Hurtado

Continue without giving the correct date …

Steve

Huh?

Martin_Hurtado

Hi Steve, I still don't have the correct result. I attached an image for comparison, please...

IFS(
ISNOTBLANK([No Orden]),
"Realizado",
TRUE,
IFS(
ISBLANK([Fecha Prog.]),
"Falta Fecha de Mantenimiento",
(TODAY() > [Fecha Prog.]),
"¡Vencido!",
(TODAY() = [Fecha Prog.]),
"¡Vence Hoy!",
(TODAY() = ([Fecha Prog.] - 1)),
"¡Vence Mañana!",
TRUE,
CONCATENATE(
FLOOR(MOD((HOUR([Fecha Prog.]-today())/24),365)*12/365)
& " (M) " &
FLOOR(HOUR(EOMONTH([Fecha Prog.],0)-[Fecha Prog.])/24))
& " (D) "
))

@Steve wrote:
Huh?

Gerardo

Hi all,

I wonder if the calendar view could be automatically fed with the dates of birth of each person registered in the DB? This is to know through the calendar the days that we have birthdays and to be able to congratulate them.

For your support, thank you.

Songklod_Cheunc

thanks

Allen_Jacques

I was getting erroneous results off by a few months until i tweaked the formula. tested by entering a date of today as a birthday... original formula was off by several months... this formula is accurate to the day.

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

Martin_Hurtado

Thank you very much ALLEN

jrobertopf

Very good. Helped me a lot. Thanks!

Jp4Real

So what if I wanted to know the Age at a specific date

In my use case it would be : Age 1st of January of this year

I tried this :

FLOOR(HOUR(Date(01/01/YEAR(TODAY()))-[DOB])/365/24)

But results were not what I expected !

Jp4Real

Got it ! :

IF(FLOOR(HOUR(DATETIME("1/1/2024 0:01")-[DOB])/365/24)>0,
FLOOR(HOUR(DATETIME("1/1/2024 0:01")-[DOB])/365/24),
0)

Jp4Real

If anyone gets to this, here's something I found, you NEED to account for leap years by dividing by 365.25 whe nyou do this, if you don't, you'll get a year jump a few days before the date (especially on older people)

also, I had to create a Virtual collumn to store the value as a DECIMAL so that I can wrap the info in a FLOOR() expression, if not, it would round it. so I created a [age.decimal] virtual collumn and hid it

here's my code with the fix

floor(HOUR(today()-[DOB]))/365.25/24

Convert Birthdate to Current Age in Years/Months format