You want to allow the user to be able to enter a date of only the current month, and all other dates should be restricted.
Eg.: The month is March in 2015 – You want to allow the user to enter a date only between 1st Mar 2015 and 31st Mar 2015.
Data Validation can be used to achieve this.
Follow these steps: –
- Select the cells on which you want to apply this data validation
- Go to Data Tab and click on Data Validation, and then..
- In Allow, select Date
- In Data, select Between
- For the Start Date, put this formula
- =DATE(YEAR(TODAY()),MONTH(TODAY()),1
- For the End Date, put this formula
- =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))
Data Validation Settings
It’s done!
Also See:
- How to create a Drop-Down list in Excel
- Allow only whole numbers using data validation
- Show Custom message in Error in Data Validation
Keep Excel-ing!
hi,
what if i wanted to have the dates of the month listed on the dropdown?
Below is the scenario:
Month : (Dropdown of months)
Date : (Dropdown of dates based on the selected month)
Is there a way that only the dates of the selected month will be reflected on the Date dropdown? I dont want to create another list of the dates for each month as it will be tedious and will be prone to error.
Thanks!
Hi Jessa, sorry for the late reply. I have written a post on this today. It’s pretty easy to create it. Do try it!
http://www.excelrush.com/how-to-make-a-dependent-drop-down-list-in-excel/