Data Validation for end of Month

[Casey]

Hi Everybody,
I need a drop down list based on the current date that would display
the previous 4 end of month dates, the current end of month date and 3
future end of month dates. And I'm hoping it would change based on the
actual current date.

As an example using today 2/8/06 the drop down would display the
following:

October 31, 2005
November 30, 2005
December 31, 2005
January 31, 2006
February 28, 2006
March 31, 2006
April 30, 2006
May 31, 2006

This could be a data validation formula or a VBA solution of some kind.

 

[Guest]

Put these formulas somewhere in your worksheet, and refer to this list in the
validation menu. If you have the list on another worksheet, you will need to
define the list name and refer to that name with an "=" in the source box.

=DATE(YEAR(TODAY()),MONTH(TODAY())-3,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())-2,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)
=DATE(YEAR(TODAY()),MONTH(TODAY()),0)
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())+3,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())+4,0)

 

[Andrew Taylor]

You can always get the last day of a month by calling it the
zeroth day of the next month. So set up a range with

=DATE(YEAR(TODAY()),MONTH(TODAY())-3,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())-2,0)
etc up to
=DATE(YEAR(TODAY()),MONTH(TODAY())+4,0)

formatted as "mmmm d, yyyy"
and use that as the source (*ListFillRange") of your dropdown

 

[Andrew Taylor]

You can always get the last day of a month by calling it the
zeroth day of the next month. So set up a range with

=DATE(YEAR(TODAY()),MONTH(TODAY())-3,0)
=DATE(YEAR(TODAY()),MONTH(TODAY())-2,0)
etc up to
=DATE(YEAR(TODAY()),MONTH(TODAY())+4,0)

formatted as "mmmm d, yyyy"
and use that as the source (*ListFillRange") of your dropdown

 

[Casey]

Andrew & Sloth,
Thank you for your replies. I'm pretty good with most functions and
features of Excel. But working with dates is my biggest weak point.
Your solution works well. Thanks for the time, experience and brain
power.

Regards,

 

[Andrew Taylor]

You're welcome

Excel's date & time functionality can be very useful and
powerful, and is well worth getting familiar with. Have a
look at Chip Pearson's page at:
http://www.cpearson.com/excel/datetime.htm (and links from there)
for some good explanations and examples.

Andrew