Need Help with DateSerial Function

[Christy]

I am having a problem increasing a date by one month. I
always want the date to be the last date of the month. I
have figured out a way to make it work but I don't
understand it. I am not sure what the zero represents.
Could someone explain this function to me & what the zero
represents. Below are the examples of what works &
doesn't.

If Date1= 2/28/2004


Does Not Work:

DateAdd("m",1,[Date1]) - Returns a date of 3/28/04. I
want it to be 3/31/04

DateSerial([Date1],([Date1]+1),[Date]) - Returns a date of
4/2/04. I want it to be 3/31/04

Does Work but do not understand Why:
DateSerial([Date1],[Date1]+2),0)
Returns: 3/31/04


Thanks!

 

[fredg]

I am having a problem increasing a date by one month. I
always want the date to be the last date of the month. I
have figured out a way to make it work but I don't
understand it. I am not sure what the zero represents.
Could someone explain this function to me & what the zero
represents. Below are the examples of what works &
doesn't.

If Date1= 2/28/2004

Does Not Work:

DateAdd("m",1,[Date1]) - Returns a date of 3/28/04. I
want it to be 3/31/04

DateSerial([Date1],([Date1]+1),[Date]) - Returns a date of
4/2/04. I want it to be 3/31/04

Does Work but do not understand Why:
DateSerial([Date1],[Date1]+2),0)
Returns: 3/31/04

Thanks!

re:> Does Work... DateSerial([Date1],[Date1]+2),0)

It does? Not for me!

DateSerial(Year([Date1]),Month([Date1])+2),0)
works for me.

DateSerial(Year(DateField]),Month(DateField)+1,1) will return the
first date of the month following the DateField month.
If you change the last argument (the 1) to a 0, then that returns the
day before the 1st of the month, i.e. the last day of the previous
month.
So, DateSerial(Year(Date()),Month(Date()),0) will return the last day
of the month, no matter how many days in that month., i.e. the 28th ,
29th, 30th, or 31st.

You added 2 months to the February DateField month (=April) so the
result is the last day of the month before April, March 31st.

 

[Guest]

DateSerial is a very unsophisticated function so to ask for the 0th Day of
any given month will invoke the logic 0 = 1 - 1 (the 0th day of the month is
1day before the 1st of the month), which produces the date for the last day
of the preceeding month

Hope This Helps
Gerald Stanley MCSD

 

[Rick Brandt]

DateSerial is a very unsophisticated function so to ask for the 0th Day of
any given month will invoke the logic 0 = 1 - 1 (the 0th day of the month is
1day before the 1st of the month), which produces the date for the last day
of the preceeding month

You think that makes it "unsophisticated"? It's working exactly as intended and
is one of the handiest functions for manipulating dates I've ever seen. Every
argument works the same way. You can hand DateSerial the 60th of a month and it
will accurately return the correct date value. Likewise any argument can be fed
a negative value and return the correct value even accounting for leap years.