Dropdown menu with dates

Question

I created a dropdown where the user can enter dates. The Data Validation is from the table with dates that the user could enter. But I want them to be able to enter as long back as possible, until today. How do I do that? And it would be even better if Today() would appear as "Today" in the list.

Answer 1

I hope you will have the time to help me! Thank you.

Answer 2

Thank you for your answer! Unfortunately I couldn't implement your code.

So, I have the list with dates, let's say Today()-2, Today()-1, Today(), Today()+1, Today()+2 in another sheet.

I want the user to be able to input even older dates manually, but it should not be shown in the dropdown list.

The dropdown's where the user input is expected are presented in a table on the sheet "Lista". The StartDate dropdown form is at B9 and will expand down with the table.

The list with those dates I want to show in the dropdown is located in another sheet called "ValidDates".

It would also be nice to show Today()-1 as Yesterday, Today() as Today and Today()+1 as Tomorrow in the dropdown. And the rest in the original date format. 

Here is my excel file

https://easyupload.io/avwua8

Answer 3

Hello Robin

I am V. Arya, Independent Advisor, to work with you on this issue. I am not sure whether your minimum is TODAY and you need to allow maximum from your table.

However, assuming maximum is from table and TODAY is minimum, you can use following formula for DV (ranges are my sheet, you will need to change)

=AND($A$2<=MAX(E:E),$A$2>=TODAY())

Using CF, you can use following formula and give "TODAY" in custom to display TODAY if date is of today

=$A$2=TODAY()

I have uploaded the sample file for you - https://1drv.ms/x/s!AldvjX7HG_m7gSsTfdyAs62c9VY...