Reputation: 1511

Number of days in a year Excel VBA

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?

Upvotes: 1

Answers (5)

JPR

Reputation: 642

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

Upvotes: 1

user4039065

Reputation:

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

Upvotes: 3

CallumDA

Reputation: 12113

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:

if (year is not divisible by 4) then (it is a common year)

else if (year is not divisible by 100) then (it is a leap year)

else if (year is not divisible by 400) then (it is a common year)

else (it is a leap year)

Upvotes: 4

Nathan_Sav

Reputation: 8531

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

Upvotes: 7

Scott Craner

Reputation: 152505

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)

Upvotes: 2

Number of days in a year Excel VBA

Answers (5)

Related Questions