Data Validation List question for DATES

[Tony C]

Hello -

I want to create a Data Validation List that would allow the user to select dates from a drop down that would give a list of all dates in the current month from the first day of the month through yesterday only.

IE... if today was February 4th, 2014 the only options in the drop down would be:

2/1/14
2/2/14
2/3/14
2/4/14

Please help!

Thank you

 

[Somendra Misra]

Hi Tony C,

Welcome to the forum.

Please have a look of attached file. Data validation is in cell C1. I had created a dynamic list of dates in colunm A which will generate dates based on today's date, which is passed to a named range, which is used for data validation list.

Just advise if you have any issue.

Regards,

 

[NARAYANK991]

Hi Misra ,

We can use the following formulae :

In the first cell : =EOMONTH(TODAY(),-1)+1

Thereafter : =IF(MAX($A$1:A1)+1<TODAY(),A1+1,"")

Narayan

 

[Somendra Misra]

Another nice solution Narayan Sir.

Regards,
SM

 

[chirayu]

@NARAYANK991

Here's a diff version for achieving the same thing hope you like it

A1 -

=("01-"&TEXT(TODAY(),"mmm-yy"))*1

A2 etc -

=IFERROR(IF(A1+1<TODAY(),A1+1,""),"")

 

[Tony C]

This was a huge help.

Thanks everyone

 

[Tony C]

Hello -

I noticed an issue with this today... in using all of the above formulas, none of them include todays date in the listing that populates?

 

[NARAYANK991]

Hi Tony ,

Specifically with reference to the formula I posted , which was :

=IF(MAX($A$1:A1)+1<TODAY(),A1+1,"")

changing it to :

=IF(MAX($A$1:A1)+1<=TODAY(),A1+1,"")

where an equality has been included , will mean that today's date will also be present in the list.

Narayan

 

[Tony C]

Nevermind... I just realized that's what I WANT it to do.

Man I am losing my mind staring at these spreadsheets all day.

^.^