give back times of current month automatically

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

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

--

HTH

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


"Markus Scheible" <[email protected]> wrote in message
news:[email protected]...
> 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

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



>-----Original Message-----
>day(Dateserial(Year(Date),Month(Date)+1, 0))
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"Markus Scheible" <[email protected]>
wrote in message
>news:[email protected]...
>> 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
>
>
>.
>

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)


"Markus Scheible" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> >-----Original Message-----
> >day(Dateserial(Year(Date),Month(Date)+1, 0))
> >
> >--
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"Markus Scheible" <[email protected]>
> wrote in message
> >news:[email protected]...
> >> 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
> >
> >
> >.
> >

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

--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>
> "Markus Scheible" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> > >-----Original Message-----
> > >day(Dateserial(Year(Date),Month(Date)+1, 0))
> > >
> > >--
> > >
> > >HTH
> > >
> > >RP
> > >(remove nothere from the email address if mailing direct)
> > >
> > >
> > >"Markus Scheible" <[email protected]>
> > wrote in message
> > >news:[email protected]...
> > >> 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
> > >
> > >
> > >.
> > >
>
>

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 :o)

Heavy thanks,

Markus

>-----Original Message-----
>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
>
>--
>Regards,
>Tom Ogilvy
>
>"Bob Phillips" <[email protected]> wrote
in message
>news:[email protected]...
>> 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)
>>
>>
>> "Markus Scheible" <[email protected]>
wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> >
>> > >-----Original Message-----
>> > >day(Dateserial(Year(Date),Month(Date)+1, 0))
>> > >
>> > >--
>> > >
>> > >HTH
>> > >
>> > >RP
>> > >(remove nothere from the email address if mailing
direct)
>> > >
>> > >
>> > >"Markus Scheible"
<[email protected]>
>> > wrote in message
>> > >news:[email protected]...
>> > >> 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
>> > >
>> > >
>> > >.
>> > >
>>
>>
>
>
>.
>