February day 31

Easiest would be to have a single validated date entry cell.

However, using your posted workbook, perhaps Conditional Formatting?
(see the attached workbook)

Please Login or Register  to view this content.

Now, whenever the 3 input cells have values, the CF will flag invalid dates.

Is that something you can work with?

Your template uses 3 fields to build a date: B2, C2, and D2

The conditional format formula checks that:
• 3 values exist in those 3 cells
• A valid date can be built using those 3 values

It checks for a valid date by constructing "date text" from the cells
and using that text string as the parameter of the DATEVALUE function:
DATEVALUE($B2&"-"&$C2&"-"&$D2)

Example:
B2: 25
C2: January
D2: 1970

Then:
=DATEVALUE($B2&"-"&$C2&"-"&$D2)
=DATEVALUE("25"&"-"&"January"&"-"&"1970")
=DATEVALUE("25-January-1970")
=25593 (The Excel date serial number for 25-January-1970)

If the text cannot be a valid date (eg 31-February-1970), an error is returned.

Note:
The same exact formula must be applied to each cellin B2, C2, and D2.
The dollar signs ($) in this formula DATEVALUE($B2&"-"&$C2&"-"&$D2)
lock the column references as the formula is copied across.

For more information, look up "range references" in Excel Help and check the sections on relative vs absolute references.

I hope that helps.

would be helpful if the sheets weren't hidden and password protected!
but in your days validation list 1-31 replace
29 with

Please Login or Register  to view this content.

it will evaluate year to see if its a leapyear and give 29 for february if it is
---------
replace 30 with

Please Login or Register  to view this content.

and 31 with

Please Login or Register  to view this content.

then rearange sheet to make them put in year/ month/ day in that order then only the appropriate days will display

True, protected workbooks only make our job more difficult.

Fortunately, in this case the one hidden sheet only holds the DV list sources