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

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