Date of birth data validation

[Shaikh Aziz]

Dear team,

I have in column A2 day, column B2 month, column C3 year, and in coloumn O i have date from 00 to 31, in coloumn P i have month from 00 to 12, in Q i have year from 00 & 1955 to 2075, i have created name manager of column O as day, column P as month, and column Q as year,
Question: i want to give a birth date validation drop down list in column A2, B2 & C2 respectively day, month, & year, but when i will select e.g. feb 2022 from dropdown list of month & year the day should show only 28 days it should not show till 31 days. because 2022 feb month has leaping year.

for better understanding please refer screenshot.

Baiscally i want days should be show accordingly selected months & year from drop-down list.

Thanks & Regards,

 

[Misca]

You could use a named range with something like

=OFFSET(Sheet1!$O$2,0,0,DAY(EOMONTH(DATE(Sheet1!$C$2,Sheet1!$B$2,1),0)))

as the formula. However, to make this work you'd need to have selected at least the month before you can access the dates dropdown. Also, the drop downs are okay when there's only a few values to choose from but with 31 days and more than 50 years... I'd hate to use something like that.
It would be a lot easier for the users as well as yourself if you could just let the users enter the date in a cell and use a data validation to check the date is valid and between the given days.

 

[Shaikh Aziz]

You could use a named range with something like

=OFFSET(Sheet1!$O$2,0,0,DAY(EOMONTH(DATE(Sheet1!$C$2,Sheet1!$B$2,1),0)))

as the formula. However, to make this work you'd need to have selected at least the month before you can access the dates dropdown. Also, the drop downs are okay when there's only a few values to choose from but with 31 days and more than 50 years... I'd hate to use something like that.
It would be a lot easier for the users as well as yourself if you could just let the users enter the date in a cell and use a data validation to check the date is valid and between the given days.

Thankyou so much misca, it worked, and i can understand that we are hating so much unused numbers in data validation, but that was the based on condition & criteria it will be using for calculating premium of insurer based on as much as older age & as much as future age.
once again thankyou so much for your co-operation.