Validate Month

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
839
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
365
Platform
Windows
If it says "July" in F3, and not a date, then try:

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

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Excel Workbook
FG
21InputData validation list
2202/06/2010Jun
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,698
Messages
5,512,923
Members
408,922
Latest member
EAhrens

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top