give back times of current month automatically

[Markus Scheible]

Hi Newsgroup,

I need a code which automatically gives me the number of
days from the current month (e.g. 31 in January, 28 in
February a.s.o.)... some days before, I got the following
code:

Day(DateSerial(Year(Date), Month(Date), 0))


But the problem is that this gives back "31" even today -
when "28" would have been very much better ;o)

Does anyone have a better idea?

Thanks in advance and best regards

Markus

 

[Bob Phillips]

day(Dateserial(Year(Date),Month(Date)+1, 0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Markus Scheible]

Hi Bob,

well, that does not automatically change into 31 when it
is March 1st... so I will have the same problems like
before...

I would need something that *automatically* finds out
which month is "today" and which gives me the days... and
which doesn't need to be changed by the beginning of a new
month... you see?

Nevertheless, thanks for your idea.

Best

Markus

 

[Bob Phillips]

Sorry mate, but that is exactly what that does. Date is today's date, and
it works out the days in the month by going to the 0th day of the month
after today's month, which is the last day of this month, and extract that
day number.

On the 1st March that code will return 31 if you run it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Just to add to Bob's excellent advice, try this:

Sub ABCD()
sStr1 = ""
For i = 1 To 12
dt = DateSerial(2005, i, 1)
sStr = "Last day of " & _
Format(dt, "mmmm") & " is " & _
Day(DateSerial(Year(dt), Month(dt) + 1, 0))
sStr1 = sStr1 & sStr & vbNewLine

Next
MsgBox sStr1, , "Last Day of the Month for Year 2005"
End Sub

 

[Markus Scheible]

Hi Tom, hi Bob,

thanks a lot for your help - it was a little diffusing for
me that DateSerial counts the 0st day of a month... so I
thought it may have been an error...

But thanks to you I am fully satisfied now )

Heavy thanks,

Markus