Skip to main content
Solved

age in YY.MM format

  • August 26, 2023
  • 4 replies
  • 80 views
D
Forum|alt.badge.img+4

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:

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 🙏

 

Best answer by Sho

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 )

 

 

4 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • August 26, 2023

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 )

 

 

D
Forum|alt.badge.img+4
  • Dimi_Timeline
  • Author
  • Participating Frequently
  • August 28, 2023

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

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • August 28, 2023

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

 

A
A
Forum|alt.badge.img+17
  • Andy_Lin1
  • Inspiring
  • August 29, 2023

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)

 

Terms & ConditionsAccessibility statement