Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
4 years ago
Solved

Today - Column Date = Age

Hi 

 

I have a table with a date issued column , however some of the fields are blank .  I would like to use a custom column function to do the following.

 

If date column - blank return blank else todays date - date issued / 365.24 = date in years (1 decimal point)

 

Example  of table and expected result

Issue DateAge in years
01/02/20220.3
02/02/20211.3
03/02/20202.3
  
09/02/20220.3
10/02/20220.3
11/02/20193.3
12/02/20211.3
13/02/20220.3
  
15/02/201012.3
15/02/20220.3
13/02/20184.3
14/02/20175.3
15/02/20139.3
16/02/201210.3

 

thank you

 

Richard

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    If you have already converted your column to date column, then blanks will be converted to null.

    In this case, use below formula

    = Number.Round(Duration.Days(Date.From(DateTime.FixedLocalNow())-[Issue Date])/365.24,1)

    But looks like, you haven't converted the column to date, then you will have to use below formula

    = Number.Round(Duration.Days(Date.From(DateTime.FixedLocalNow())-(if [Issue Date]="" then null else Date.From([Issue Date])))/365.24,1)

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    If you have already converted your column to date column, then blanks will be converted to null.

    In this case, use below formula

    = Number.Round(Duration.Days(Date.From(DateTime.FixedLocalNow())-[Issue Date])/365.24,1)

    But looks like, you haven't converted the column to date, then you will have to use below formula

    = Number.Round(Duration.Days(Date.From(DateTime.FixedLocalNow())-(if [Issue Date]="" then null else Date.From([Issue Date])))/365.24,1)