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??
=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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.