a DateAdd question?

  • Thread starter Thread starter Geoff Cox
  • Start date Start date
G

Geoff Cox

Hello

I have found

Between DateAdd("m",-11,Date()) AND DateAdd("m",-12,Date())

which, if I am right, gives me events within the period of 1 month, 12
months ago but would like to find those events between the 1 and the
last day of the month 12 months ago.

So this month being December I want to find events between the 1st and
the 31st of December 2006. Plus a formula which will work for January
2008, February 2008 etc.

Ideas please!

Thanks

Geoff
 
If you were to run that today (4 Dec, 2007), you'd get those events between
4 Dec, 2006 and 4 Jan, 2007.

What you want is:

Between DateSerial(Year(Date), Month(Date)-12, 1) And DateSerial(Year(Date),
Month(Date)-11, 0)

Don't worry: DateSerial will handle everything correctly for all months.
 
Try DateSerial instead of DateAdd (watch for wrapping in your newsreader;
this is all on one line:
Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

This will give you from the first to the last day of the current month. The
0 at the end of the expression is the day before the first day of the
specified month. Given today's date, Year is 2007, Month + 1 is January
(December + 1), and 0 is the day before January 1. The advantage of using
the zero is that it works with any month, no matter the number of days.
 
Try DateSerial instead of DateAdd (watch for wrapping in your newsreader;
this is all on one line:
Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

This will give you from the first to the last day of the current month. The
0 at the end of the expression is the day before the first day of the
specified month. Given today's date, Year is 2007, Month + 1 is January
(December + 1), and 0 is the day before January 1. The advantage of using
the zero is that it works with any month, no matter the number of days.
Click to expand...

My thanks to you both!

Cheers

Geoff
 
Back
Top