Forum Discussion

adonee's avatar
adonee
Copper Contributor
Jul 12, 2023
Solved

Trying to calculate tenure by year

What formula can I use to calculate years between two dates and use current date if cell is blank?

excel
  • mathetes's avatar
    mathetes
    Jul 12, 2023

    adonee 

    Assume start date is in cell A1, and A2 either is blank or holds the end date. The YEARFRAC function calculations the number of years as a decimal number, along the lines of 10.1234

    =IF(A2="",YEARFRAC(A1,TODAY(),1),YEARFRAC(A1,A2,1))

     

    Here's another approach if you have a current version of Excel, one that can take advantage of the LET function.

    =LET(end,IF(A2="",TODAY(),A2),YEARFRAC(A1,end,1))

     

     

3 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jul 12, 2023

    adonee 

    Assume start date is in cell A1, and A2 either is blank or holds the end date. The YEARFRAC function calculations the number of years as a decimal number, along the lines of 10.1234

    =IF(A2="",YEARFRAC(A1,TODAY(),1),YEARFRAC(A1,A2,1))

     

    Here's another approach if you have a current version of Excel, one that can take advantage of the LET function.

    =LET(end,IF(A2="",TODAY(),A2),YEARFRAC(A1,end,1))

     

     

Resources