YEARFRAC

  • Thread starter Thread starter DKM
  • Start date Start date
D

DKM

Hi!

I am trying to use the YEARFRAC function in Excel.

I have entered the following:

=YEARFRAC("9/9/1999", "4/4/2004",3)

which yields 4.57260274.

Perfect.

But going all through the entire spread sheet to change
the last date to present date is tedious. How do I tell
the string to use today's date as end_date? I've tried
putting in "NOW()" or "TODAY()" but no joy. Also read
through all the help topics...

Any advice?

Thanks!

DKM
 
=YEARFRAC("9/9/1999", TODAY(),3)

really should do it!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi DKM!

Just a warning. Basis 1 might be better (Actual/Actual) rather than
Basis 3 (Actual/365) but YEARFRAC has problems with all bases where
the number of years exceeds 1.

To get an accurate count use:

=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))


Even then, I've had to avoid DATEDIF with yd arguments as that
produces errors as well.

As an alternative use a UDF by Myrna Larsen:

Function YearDiff(ByVal StartDate As Date, _

Optional ByVal EndDate As Date = #1/1/100#) As Double

'modified 02/01/2003

Dim AnnDay As Long

Dim AnnMonth As Long

Dim AnnYear As Long

Dim ltemp As Date

Dim NextAnn As Date

Dim PrevAnn As Date



If EndDate = #1/1/100# Then EndDate = Date



'put in right order if necessary

If StartDate > EndDate Then

ltemp = StartDate

StartDate = EndDate

EndDate = ltemp

End If



'get anniversary date in ending year

AnnYear = Year(EndDate)

AnnMonth = Month(StartDate)

AnnDay = Day(StartDate)

'assume it's already occurred

PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)



If PrevAnn <= EndDate Then

'assumption that it's past was correct

'next anniversary is 1 year in the future

NextAnn = DateSerial(AnnYear + 1, AnnMonth, AnnDay)

Else

'wrong -- we calculated the *next* anniversary

NextAnn = PrevAnn

AnnYear = AnnYear - 1

PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay)

End If



YearDiff = AnnYear - Year(StartDate) + _

(EndDate - PrevAnn) / (NextAnn - PrevAnn)



End Function 'YearDiff


We covered the problems with YEARFRAC in:

http://tinyurl.com/2nstc

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top