Validation List - Date based formula

**Dan17602** · 10-17-2011, 12:00 PM

Hi,

Im wondering if it is possible to have a formula within the Validation drop down list that will display a list a months. Next to each month has a date range (from - to). Is there a lookup/if function that would include that month if it is over a certain date

Example:

January - 02/01/2011 - 31/01/2011
February - 01/02/2011 - 28/02/2011

Today is 30th Jan so the dropdown will only display "January"

Any help would be much appreciated
Thanks,

**6StringJazzer** · 10-17-2011, 12:11 PM

You would have to set up a list of cells containing a formula that would give you these values, then point data validation to that list.

But I can't figure out how you are determining the from-to dates or what months are supposed to be included in the list. You omitted Jan 1, maybe that's because it's a holiday?

If today is Jan 30, you are only including one month in the list? Not sure why you need to display a list to choose from if there's only going to be one item in it....

**Dan17602** · 10-17-2011, 12:51 PM

Hi 6StringJazzer,

Thank you for your prompt reply to this

Basically the dates next to the months represent the financial calendar - so from the dropdown what my aim is instead of having to add each month every 4/5 weeks is for the list to pre-populate the months based on the current day. So yeah, if today was Jan 30 the list would only populate January because we havent reached the date that represents Feb

What type of formula could I input next to these months? The layout I have is...

Month name when the month starts when the month ends etc etc

Thanks,

**6StringJazzer** · 10-17-2011, 07:56 PM

I'm still not quite getting this.

Do you want your list to start in January and exclude months that haven't started yet? So if the current day is in January you only see one month in the list, but if it's June 15 you see 6 months?

Attached is a mock-up of how this could work.

**Dan17602** · 10-25-2011, 04:48 AM

Hi 6StringJazzer,

Apologies for the late reply. What you have come up with on the excel document is exacly what I was after. Thank you very much for your support on this

Regards, Dan