Finding the number of days in a month 3

[djburnheim]

I know there's lots of posts about working with dates but I can't find away of working out the number of days in month using VBA in excel. I've been playing around trying to use a loop statement to count the days until the month changes but figure there must be an easier way. I did come across a post about the same problem but it was about an Excel function available in the Analysis ToolPak, EOMonth. I'm trying to right a procedure that will simply populate cells with the days of the month...any suggestions
?
Sub NewSheet(NewMonth) 'passed from user form

dRow = 1
dColumn = 2
TYear = Year(NewMonth)
TMonth = Month(NewMonth)
TDay = ** need something to work this out
LastDay = Day(DateSerial(TYeay, TMonth, TDay)

For d = 1 To LastDay
Sheets(1).Cells(dRow, dColumn) = NewMonth
dColumn = dColumn + 1
NewMonth = NewMonth + 1
Next d


End Sub

 

[xlbo]

Not sure if this is any use to you as eomonth cannot be used in VBA but this works on a worksheet:
=EOMONTH(TODAY(),0)-VALUE("01/" & MONTH(TODAY())&"/"&YEAR(TODAY()))+1

In VBA, you could just use a select case....

Select case month(date())
case 1,3,5,7,8,10,12
lDay = 31
case 4,6,9,11
lDay = 30
case 2
if i int(year(date())/4) = year(date())/4 then
lDay = 29
else
lDay = 28
end if
case else
msgbox "You've broken the calendar you fool"
end select

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[combo]

This will work:
LastDay = Day(DateSerial(TYear,TMonth+1,0))

combo

 

[chandlm]

djburnheim,

I have the following function that will return the last day of the requested month and year.

Function daysinmonth(intmonth, intyear)
intresult = DateDiff("d", DateSerial(intyear, intmonth, 1), DateSerial(intyear, intmonth + 1, 0)) + 1
daysinmonth = intresult
End Function



HTH

Matt

 

[xlbo]

chandlm - nice but why not get rif of the last line ??
Function daysinmonth(intmonth, intyear)
daysinmonth = DateDiff("d", DateSerial(intyear, intmonth, 1), DateSerial(intyear, intmonth + 1, 0)) + 1
End Function

would work just as well....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[chandlm]

xlbo,

I didn't mean to leave it in there, I was cutting it from an older function I had and forgot to remove it. Pressed submit by mistake and oops to late.


Matt

 

[xlbo]

[LOL] - never done that before

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[djburnheim]

Thanks chandlm, very simple but good solution....wis I'd thought to look at it that way, I was making it far to complicated!

 

[TonyJollans]

Hi All,

You all seemed to have missed it, but combo's solution is the one to use - it provides the last day of the month.

chandlm's code has the same construct inside it but uses a complex method to extract the day from the date.

Have a star from me, combo.

Enjoy,
Tony

 

[djburnheim]

Apologies to Combo..I did miss that solution..even more logical!

Thanks everyone
Dave

 

[combo]

Thanks Tony,
I see that I have to work on my appearance

combo