Data Validation for Today's Date


I'm trying to validate an entire column, column D, so that I can have only today's date as the dropdown =Today()

Is there a way to do that as the only selection?



What do you mean exactly? Do you mean this: have a drop-down menu that displays only the current date and then, when the user selects that date, input the current date into the cell, BUT input the actual date so that it won't change tomorrow or in the future. I ask like this because the =TODAY() function will update its date every day.
don (rep: 1969) Jun 28, '18 at 3:24 am
Thanks so much for the quick response!

What I have is a list of work. As they go down the list I want a drop down in Column D that will only give them today's date as the option and yes i would not want that date to change daily so the =Today() formula would not work. So basically I would want the (Ctrl & +) key for the dropdown I think?
Sroncey21 (rep: 62) Jun 28, '18 at 9:54 am
Try the solution from Variatus and see if that works for you, it looks pretty comprehensive for using formulas with data validation and the date.
don (rep: 1969) Jun 28, '18 at 10:26 am
Add to Discussion


Selected Answer

The fastest way to enter a static date in a cell is to press Ctl+; (that is: the Control key and, simultaneously, the semi-colon key).

If you want the current date in a drop-down list create a named range, for example:-

[A2] = TODAY()
[A3] = A2 + 7
[A4] = A3 + 7
[A5] = A4 + 7

Observe that you might copy A3 down for as far as you like. You might also let the range have only one cell.
In my example, select A2:A5. In the Name Bar (just above the A column) the cell address A2 will be displayed. Select that address (while still A2:A5 is selected) and type DropDownList (or another name you like). Enter Return and make sure that the Name Bar now shows the name you entered.

Now set up a cell with Data Validation. In the validation dialog box's Allow field select List. In the Source field enter =DropDownList (or the anme you assigned to ther ange with the list). The resulting validation drop-down will have today's date and 3 more dates, each spaced a week from the previous. The list will update daily but the value you select in the validated cell will be static.

Observe that the validated field will show a number like 43279 instead of the date you selected from the list. This isn't a mistake. It's an unformatted date. Select the cell, From the Ribbon's Home tab select Format > Format Cells. In the dialog box that opens select the Number tab. Select Date as category and choose among the pre-programmed Types. You might also select the Custom category and create your own custom date format.

Note that the named range could be on any sheet in your workbook, including a hidden sheet, or in any column of the same sheet as the validated cell, including a hidden column.


Answer the Question

You must create an account to use the forum. Create an Account or Login