Help

age in YY.MM format

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
688 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Dimi_Timeline
5 - Automation Enthusiast
5 - Automation Enthusiast

I am struggling to get a Formula that would return my age, where the number after the decimal point indicates the number of months in duodecimal format. For example, my current age is 34 years and 3 months, therefore I want to see 34.03 as the return.

So far I've managed to only get this:

Dimi_Timeline_0-1693044489099.png

the formula I am using is:

(DATETIME_DIFF(TODAY(),{DoB},"Y"))
&"."
&((DATETIME_DIFF(TODAY(),{DoB},"M"))
-((DATETIME_DIFF(TODAY(), {DoB},"Y"))*12))
 
It is pretty close to what I am looking for, but how do I get 2 digits after the decimal point by default?

*
I understand that it may be a weird request asking to combine decimal and dozenal numeral systems like this, but would really appreciate if somebody helped me solve this one 🙏

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

It is more difficult than I thought!
How about this

String Ver.

DATETIME_DIFF(TODAY(),DoB,"Year")&
"."&
SWITCH(DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12,
  11, "",
  10, "",
  "0"
)&
IF(DATETIME_DIFF(TODAY(),DoB,"Month")>=0,
  DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12,
  DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12+12
)

Numeric Ver.

DATETIME_DIFF(TODAY(),DoB,"Year")
+
IF(DATETIME_DIFF(TODAY(),DoB,"Month")>=0,
  (DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12)/10,
  (DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12+12)/10
)
/
SWITCH((DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year"))*12,
  11, 1,
  10, 1,
  10
)

 

 

See Solution in Thread

4 Replies 4
Sho
11 - Venus
11 - Venus

It is more difficult than I thought!
How about this

String Ver.

DATETIME_DIFF(TODAY(),DoB,"Year")&
"."&
SWITCH(DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12,
  11, "",
  10, "",
  "0"
)&
IF(DATETIME_DIFF(TODAY(),DoB,"Month")>=0,
  DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12,
  DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12+12
)

Numeric Ver.

DATETIME_DIFF(TODAY(),DoB,"Year")
+
IF(DATETIME_DIFF(TODAY(),DoB,"Month")>=0,
  (DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12)/10,
  (DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12+12)/10
)
/
SWITCH((DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year"))*12,
  11, 1,
  10, 1,
  10
)

 

 

Dimi_Timeline
5 - Automation Enthusiast
5 - Automation Enthusiast

It worked for me like a charm! Thank you very much 🙌

Sho
11 - Venus
11 - Venus

I recall the existence of the MOD function.
It is simpler.

String ver.

DATETIME_DIFF(TODAY(),DoB,"Year")&
"."&
SWITCH(DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12,
  11, "",
  10, "",
  "0"
)&
MOD((DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12),12)

Numeric Ver.

DATETIME_DIFF(TODAY(),DoB,"Year")
+
MOD((DATETIME_DIFF(TODAY(),DoB,"Month")-DATETIME_DIFF(TODAY(),DoB,"Year")*12),12)/100

 

There's a terrible, brute-force way of doing the padded decimal portion:

RIGHT("0" & {Month Difference}, 2)