PDA

View Full Version : Number of days in current month



clvestin
02-02-2007, 08:47 AM
The title should say it all. How does one calculate the number of days in the current month??

gnod
02-02-2007, 09:12 AM
=DAY(DATE(YEAR(A1), MONTH(A1)+1, 0))

mdmackillop
02-02-2007, 02:14 PM
This requires the Analysis Toolpak add-in
=DAY(EOMONTH(NOW(),0))

clvestin
02-03-2007, 01:04 PM
What I was trying to do was replace a column of dates with a column of current month dates.(In my use this need tobe done on the 2nd).

so If Day(Date) = 2 Then
Range("h4:i34").ClearContents
Range("h4") = Date - 1
Range("h4:h4").AutoFill Destination:=Range("h4", "h" & Day(DateAdd("m", 1, Date) - Day(Date))) _
.Cells.Count + 3, Type:=xlFillDefault
End If


This works if the current date is less than the 28th.

Zack Barresse
02-03-2007, 01:15 PM
I use a formula for this, of course it can be done the same way via VBA, slightly different from the rest..

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)

mdmackillop
02-03-2007, 01:49 PM
Hi Clvestin
I can't reconcile your two posts. What does "replace a column of dates with a column of current month dates" mean? I honestly have no idea.

Aussiebear
02-03-2007, 02:27 PM
My guess is that on the 2nd of each month Clvestin wants the range to reflect the new months' dates. But then I'm only a bear so what would I know.:dunno

mdmackillop
02-03-2007, 02:35 PM
But then I'm only a bear so what would I know.:dunno
Do your fellow creatures really defecate in arboreal regions?

Cyberdude
02-03-2007, 05:12 PM
I use a formula for this, of course it can be done the same way via VBA, slightly different from the rest..
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1) Interesting enough, Zack, today I was having trouble getting function EOMonth to work for some reason, so I was able to substitute your formula equivalent to get me over the hump (so to speak).:friends:

Bob Phillips
02-03-2007, 05:52 PM
Interesting enough, Zack, today I was having trouble getting function EOMonth to work for some reason, so I was able to substitute your formula equivalent to get me over the hump (so to speak).:friends:

You should have used that given by gnod, more efficient.

gnod
02-03-2007, 06:04 PM
You should have used that given by gnod, more efficient.

Thanks XLD, i'm glad someone appreciate my contribution to this forum..
thanks again.. :)

Bob Phillips
02-04-2007, 04:00 AM
What I was trying to do was replace a column of dates with a column of current month dates.(In my use this need tobe done on the 2nd).

so If Day(Date) = 2 Then
Range("h4:i34").ClearContents
Range("h4") = Date - 1
Range("h4:h4").AutoFill Destination:=Range("h4", "h" & Day(DateAdd("m", 1, Date) - Day(Date))) _
.Cells.Count + 3, Type:=xlFillDefault
End If


This works if the current date is less than the 28th.

That errored on me, this didn't



If Day(Date) = 2 Then
Range("h4:i34").ClearContents
Range("h4") = Date - 1
Range("h4:h4").AutoFill Destination:=Range("h4", "h" & Day(DateAdd("m", 1, Date) - Day(Date)) + 3) _
, Type:=xlFillDefault
End If

Aussiebear
02-04-2007, 04:19 AM
Do your fellow creatures really defecate in arboreal regions?

Well we are also known as dropbears....

Zack Barresse
02-04-2007, 03:31 PM
Wow Bob, I didn't know you had a side job of splitting hairs. Do you get much business? :devil2:

Bob Phillips
02-04-2007, 05:02 PM
What do you get for repeating answers, but less efficiently :devil2::devil2:

And do you know what, my comment wasn't to you, it was aimed at CyberDude who praised your solution, yet ignored a similar (but better), and earler solution from gnod.

Zack Barresse
02-05-2007, 09:23 AM
Couldn't help the laugh. :D