As I work in the finance industry I often require calculations usin
days in a calendar year. However, I haven't been able to find
function that easily provide this integer based on a single cell dat
reference. If you know of a succint function that can replace thes
formulas please help:

Days in last year where date is 1 Jan 2003 (Cell $C$19):

=INT(($C$19-DATE(YEAR($C$19)-2,12,31))-($C$19-DATE(YEAR($C$19)-1,12,31)))

Days in current year (same date reference):

=INT(($C$19-DATE(YEAR($C$19)-1,12,31))+(DATE(YEAR($C$19),12,31)-$C$19))

Days in next year (same date reference):

=INT((DATE(YEAR($C$19)+1,12,31)-$C$19)-(DATE(YEAR($C$19),12,31)-$C$19))

Otherwise, if anyone is willing to share the VBA code to develop thes
formulas as a user defined add-in I would be most appreciable.

Thank you in advance for your help.

Cheers,
Shan

--
Message posted from http://www.ExcelForum.com

Hi Shane,

Firstly, your formulae return incorrect results for leap years and also for
the year following any leap year.

You asked for a UDF to return the number of calendar days in a year. Try:

Function DIY(aDateCell)
If IsDate(aDateCell) Then
If year(aDateCell) Mod 4 = 0 Then
DIY = 366
Else
DIY = 365
End If
Else
DIY = CVErr(xlErrNA)
End If
End Function


However, a simpler, non-function method:

..the year has 365 days unless it is exactly divisble by four, when it has
366 days


---
Regards,
Norman



..


"shanes " wrote in message
...
As I work in the finance industry I often require calculations using
days in a calendar year. However, I haven't been able to find a
function that easily provide this integer based on a single cell date
reference. If you know of a succint function that can replace these
formulas please help:

Days in last year where date is 1 Jan 2003 (Cell $C$19):

=INT(($C$19-DATE(YEAR($C$19)-2,12,31))-($C$19-DATE(YEAR($C$19)-1,12,31)))

Days in current year (same date reference):

=INT(($C$19-DATE(YEAR($C$19)-1,12,31))+(DATE(YEAR($C$19),12,31)-$C$19))

Days in next year (same date reference):

=INT((DATE(YEAR($C$19)+1,12,31)-$C$19)-(DATE(YEAR($C$19),12,31)-$C$19))

Otherwise, if anyone is willing to share the VBA code to develop these
formulas as a user defined add-in I would be most appreciable.

Thank you in advance for your help.

Cheers,
Shane


---
Message posted from http://www.ExcelForum.com/

Given ONLY the year in cell A1, say 2003,

=DATEDIF(DATE(A1,1,1),DATE(A1+1,1,1),"d")

If A1 contains a complete date, replace A1 in the above formula with YEAR(A1),
i.e.

=DATEDIF(DATE(YEAR(A1),1,1),DATE(YEAR(A1)+1,1,1)," d")

On Thu, 29 Jul 2004 19:49:00 -0500, shanes
wrote:

As I work in the finance industry I often require calculations using
days in a calendar year. However, I haven't been able to find a
function that easily provide this integer based on a single cell date
reference. If you know of a succint function that can replace these
formulas please help:

Days in last year where date is 1 Jan 2003 (Cell $C$19):

=INT(($C$19-DATE(YEAR($C$19)-2,12,31))-($C$19-DATE(YEAR($C$19)-1,12,31)))

Days in current year (same date reference):

=INT(($C$19-DATE(YEAR($C$19)-1,12,31))+(DATE(YEAR($C$19),12,31)-$C$19))

Days in next year (same date reference):

=INT((DATE(YEAR($C$19)+1,12,31)-$C$19)-(DATE(YEAR($C$19),12,31)-$C$19))

Otherwise, if anyone is willing to share the VBA code to develop these
formulas as a user defined add-in I would be most appreciable.

Thank you in advance for your help.

Cheers,
Shane


---
Message posted from http://www.ExcelForum.com/