Date Criteria

[Guest]

Please help novice.

The following criteria returns LAST year, THIS month, EACH date -
TO-CURRENT-DATES only.

Between DateSerial(Year(Date())-1,Month(Date()),1) And DateAdd("m",-12,Date())

I would like it to return LAST year, THIS month, EACH date, ALL DATES.

I cannot figure it out. Please light this darkenss for me.

 

[Jeff Boyce]

Chris

I'm not clear what you mean by EACH DATE, ALL DATES.

Are you saying you want everything in the same month from last year? If so,
you need the first day of the current month and the last day of the current
month, for the current year - 1.

A shortcut way to get the last day of the current month is to set
DateSerial() to the NEXT month, and the day in DateSerial() to "0" (zero).
The zero'th day of a month is the previous month's ending day.

 

[Chris2]

Please help novice.

The following criteria returns LAST year, THIS month, EACH date -
TO-CURRENT-DATES only.

Between DateSerial(Year(Date())-1,Month(Date()),1) And DateAdd("m",-12,Date())

I would like it to return LAST year, THIS month, EACH date, ALL DATES.

I cannot figure it out. Please light this darkenss for me.

Chris,

Here's the bare bones of a VBA function:

Public Function LastDayOfMonthLastYear(InDate As Date) As Date

Dim OutDate As Date

OutDate = DateSerial(Year(DateAdd("M", 1, InDate)) - 1 _
, Month(DateAdd("M", 1, InDate)) _
, 1) - 1

LastDayOfMonth = OutDate

End Function



Your expression becomes:

Between DateSerial(Year(Date())-1,Month(Date()),1) And
LastDayOfMonthLastYear(Date())


Alternately, you can use the expression in the function above
directly in your expression:

Between DateSerial(Year(Date())-1,Month(Date()),1)
And DateSerial(Year(DateAdd("M", 1, InDate)) - 1,
Month(DateAdd("M", 1, InDate)), 1) - 1


Note: I largely assumed you wanted the 1st date to the last date of
the same month as now in the past year.

Sincerely,

Chris O.

 

[Guest]

That makes sense. I am missing something, though. I get last year, this
month. But I cannot figure how to make it next month, last year.

Forsaking the Day for testing, I can return the first day of last year, this
month by using ",1" with month; the second day of last year this month by
using ",2" etc.

Day "0" makes sense, if I could get next month returned.

This returns wrong number of arguments:
DateSerial(Year(Date())-1,Month(Date()),1,Day(Date()),0)

HELP! I must be dim today.

 

[Chris2]

That makes sense. I am missing something, though. I get last year, this
month. But I cannot figure how to make it next month, last year.

Forsaking the Day for testing, I can return the first day of last year, this
month by using ",1" with month; the second day of last year this month by
using ",2" etc.

Day "0" makes sense, if I could get next month returned.

This returns wrong number of arguments:
DateSerial(Year(Date())-1,Month(Date()),1,Day(Date()),0)

HELP! I must be dim today.

Chris,

DateSerial(Year(DateAdd("M", 1, Date)) - 1, Month(DateAdd("M", 1,
Date)), 1) - 1


Sincerely,

Chris O.

 

[Guest]

Again, thank you.

That doesn't work, in any changed form I know of. The following does,
returning the first date of this month last year.

DateSerial(Year(Date())-1,Month(Date()),1)

How do I modify THAT code to get the last day of this month last year?

 

[John Spencer]

DateSerial(Year(Date())-1,Month(Date())+1,0)

That should return November 30, 2004 when the date is anytime in Nov 2005

As far as DateSerial is concerned the 0 day of any month is the last day of
the previous month. Think of it as
DateSerial(Year(Date())-1,Month(Date())+1,1) -1 or

DateSerial(Year(Date())-1,Month(Date())+1,1-1)

 

[Guest]

Thanks so much John. Did the trick.

 

[Chris2]

Again, thank you.

That doesn't work, in any changed form I know of. The following does,
returning the first date of this month last year.

DateSerial(Year(Date())-1,Month(Date()),1)

How do I modify THAT code to get the last day of this month last year?



--
Thanks for your help,
Chris


:

Chris,

I've just tested it again, and it works.

What results is . . .

DateSerial(Year(DateAdd("M", 1, Date)) - 1, Month(DateAdd("M", 1,
Date)), 1) - 1

.. . . giving you?


Sincerely,

Chris O.