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

[opie_18]

for January it gave me 10 days....Hang on

change your Date to = my date field but...
this gives me 29 days for January

dDrptDate = Range("G2").Value
    dFirstOfCurrMonth = DateAdd("d", -Day(dDrptDate), dDrptDate) + 1
    dLastOfCurrMonth = DateAdd("m", 1, dFirstOfCurrMonth) - 1
    iDays = Day(dLastOfCurrMonth - dFirstOfCurrMonth)
    'iDays = DateDiff("d", dFirstOfCurrMonth, dLastOfCurrMonth + 1) '28
    MsgBox (dDrptDate & "Days in month" & iDays)

[opie_18]

Got it buddy... thank you so much... here's what i did

dDrptDate = Range("G2").Value
    dFirstOfCurrMonth = DateAdd("d", -Day(dDrptDate), dDrptDate) + 1
    dLastOfCurrMonth = DateAdd("m", 1, dFirstOfCurrMonth) - 1
    iDays = (Day(dLastOfCurrMonth) - Day(dFirstOfCurrMonth)) + 1
    'iDays = DateDiff("d", dFirstOfCurrMonth, dLastOfCurrMonth + 1) '28
    MsgBox ("Month " & Month(dDrptDate) & " has Days " & iDays & " in it")

[RobDog888]

Cool! Nicer way to do it.

Ps, dont forget to Resolve your thread.

[techgnome]

Seems like a lot of work to me.... It took me a while to see just how it was working.... using the negative of Day() to back track to the first - slick.... here's how I've done it in the past:

Dim datCurDate As Date
Dim lngDayCount As Long 'I use long as a force of habit, an integer would suffice as well
 
 
datCurDate = Now
'Get the first of the month
datCurDate = DateSerial(Year(datCurDate), Month(datCurDate), 1)
'Get the difference between the first and last days
lngDayCount = DateDiff("d", datCurDate, DateAdd("m", 1, datCurDate))
MsgBox lngDayCount

Correctly returned 28 for the month of Feb.

Tg

[opie_18]

very nice...

TY

[opie_18]

how do you close this thread since we've solved it???

[techgnome]

Edi the first post. Add "[RESOLVED]" to the subject line, then select the green check mark in the "Post Icons" section, click the update button. And yer done.

Tg