Validate Month

proctk

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

swayp11

Board Regular
Joined
Apr 27, 2009
Messages
107
Office Version
  1. 365
Platform
  1. 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
  1. 365
Platform
  1. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top