Data Validation on Month, Ignore Year

[MrThorne]

I am creating a master workbook to be used for any year, and is broken down such that each sheet is assigned a month. Along with other information, the user is required to input the date that they collected their data (may not be the same day that they are entering data).

-I would like to set up data validation on a column that verifies the date they entered is on the correct sheet (January data on the January sheet).

-I also need the data assigned to a specific month, but to ignore the year. The master sheet will be finished this year, but if someone were to continue inputting data next year or the year after, I want it to still verify the month.

-The solution cannot involve the use of macros.

When using data validation on a range of dates, it limits me to dates within a year. Is there a way to work around this?

 

[Chihiro]

Assuming A1 contains the month (Ex. January).
Assuming A2~ contains the date info.
Data validation for A2, =TEXT(A2,"mmmm")=$A$1

 

[MrThorne]

If I understand correctly, the proposed solution is to split the month and day into two separate cells. I would then be verifying if the number entered was from 1-31 (depending on month).

This could work, but is not ideal. Is there any way to use one cell for the entire date?

 

[Chihiro]

No. You use header or holder cell to keep month on a sheet as reference. Since the formula extracts only month as text string, it does not care if you have date, year or time in the cell.

 

[MrThorne]

Perfect! Thanks!