Days in a month

[opie_18]

Hello,

Is there a formula in excel or vba that will return how many days are in a specified month...

Thanks so much....

John

[RobDog888]

Use this for the current month...

Dim dFirstOfCurrMonth As Date
    Dim dLastOfCurrMonth As Date
    dFirstOfCurrMonth = DateAdd("d", -Day(Date), Date) + 1
    dLastOfCurrMonth = DateAdd("m", 1, dFirstOfCurrMonth) - 1
    MsgBox DateDiff("d", dFirstOfCurrMonth, dLastOfCurrMonth + 1)

[opie_18]

ok so I could do this to just get the number of days i.e. 30 or 31 or 28

Dim Day as Integer
 
Day = dLastOfCurrMonth + 1

I will need that number so that I can do a for loop and add it to the date so that I have jan 1, jan 2, jan 3, jan 4... I beleive that the date is converted to an integer because it's pulled in from a text file... so if that's the case I should be good....

Thank you

[RobDog888]

Not quite, that is a reserved keyword (Day). If you change your var declaration then yes.

Dim iDays As Integer
Dim dFirstOfCurrMonth As Date
Dim dLastOfCurrMonth As Date
dFirstOfCurrMonth = DateAdd("d", -Day(Date), Date) + 1
dLastOfCurrMonth = DateAdd("m", 1, dFirstOfCurrMonth) - 1
iDays = DateDiff("d", dFirstOfCurrMonth, dLastOfCurrMonth + 1) '28
'Or
iDays = Day(dLastOfCurrMonth + 1

[opie_18]

I'll let you know how it goes...

Thanks,

John

[RobDog888]

Ok, 's