Forum Discussion

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

adonee
Copper Contributor
Jul 19, 2023
Thank You! mathetes
SergeiBaklan
Diamond Contributor
Jul 12, 2023
mathetes

Or

=YEARFRAC(A1, A2 + (A2="")*TODAY(), 1)