Number of days in a year Excel VBA

1
votes

I am trying to calculate the number of days for a dedicated year based on a date, e.g. 2015 using excel vba. 2015 should have 365 days which is a non-leap year and 2016 are 366 which is a leap year.

A date would be e.g. 01.02.2015 and based on the year 2015 I would like to know how many days are in 2015. Is there a way doing it in vba?

5
vbaexcel
datediff function – Nathan_Sav

5 Answers

7
votes

or 

Public Function daysinyear(lngyear As Long) As Long
    daysinyear = DateDiff("d", DateSerial(lngyear, 1, 1), DateSerial(lngyear + 1, 1, 1))
End Function
4
votes

This works for most purposes e.g. 1901-2099:

=IF(MOD(YEAR(A1),4),365,366)

As @tigeravatar pointed out, for more rigorous logic try this:

=IF(MOD(YEAR(A1),4),365,IF(MOD(YEAR(A1),100),366,IF(MOD(YEAR(A1),400),365,366)))

Source: Wikipedia's handy algorithm:

  1. if (year is not divisible by 4) then (it is a common year)
  2. else if (year is not divisible by 100) then (it is a leap year)
  3. else if (year is not divisible by 400) then (it is a common year)
  4. else (it is a leap year)
3
votes

The DAYS function was introduced with Excel 2013.

=DAYS(DATE(2017, 12, 31), DATE(2017, 1, 1))+1

The NETWORKDAYS.INTL function was introduced at the same time. Set it to include all days.

=NETWORKDAYS.INTL(DATE(2017, 1, 1), DATE(2017, 12, 31), "0000000")
2
votes

Just subtract 12/31 of the year previous from 12/31 of the year desired:

=DATE(YEAR(A2),12,31)-DATE(YEAR(A2)-1,12,31)

enter image description here

0
votes

The question asks for a VBA solution. Here is one that determines the number of days based on the existence of the leap day:

Public Function daysinyear(lngyear As Long) As Long
    daysinyear = 368 - Month(DateSerial(lngyear, 2, 29))
End Function