Validate Month

[proctk]

Hi, I want to set a column to validate the month value so that it must equal the month in cell F3 Month value = "July".

example

Column A date value

28/07/2010.

If the value entered in column A is not a day in the month of July then error message.

Thank you for any help

 

[swayp11]

If it says "July" in F3, and not a date, then try:

=IF(TEXT(A1,"mmmm")=$F$3,"",NA())

 

[proctk]

Would I put this in the validation under "Custom"

 

[swayp11]

I apologize, I thought you wanted a helper column in column B.

To put it in Data Validation, choose Data Validation, and choose "Custom". Then put this formula in:

=IF(TEXT(A1,"mmmm")=$F$3,TRUE,FALSE)

This will stop anything that is not the same month as F3. Also, you can use the error message tab of the Data Validation to customize your message.

HTH

 

[scottylad2]

Would I put this in the validation under "Custom"

Create a list of dates, I used 01/01/2010 amd the first of the month for the rest of the year. in Cell A1 or wherever you want it, go to the Data Validation Menu, and insert your list into Cell A1 as a drop down list.

in the Format menu, go to custom, and type in mmm you should now have a dropdown displaying only the months of the year.

In B1 again go to the Data validation box, in the Allow drop down, select Dates.

In the From Date, type =Eomonth(A1,-1)+1
and in the To Date type =Eomonth(A1,0) which forces the then click ok.

You should now only be able to insert dates between the 1st and last of whatever month is displaying in A1

 

[scottylad2]

Excel Workbook
	F	G
21	Input	Data validation list
22	02/06/2010	Jun
Course Dates

It won't allow me to display the error message, but it works like a charm. You can then customize the error alert to suit