Date of birth data validation

Shaikh Aziz

New Member
Joined
Dec 18, 2020
Messages
28
Office Version
  1. 2007
Platform
  1. Windows
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,
 

Attachments

  • sample1.JPG
    sample1.JPG
    91.4 KB · Views: 1

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,689
You could use a named range with something like
Excel Formula:
=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.
 
Solution

Shaikh Aziz

New Member
Joined
Dec 18, 2020
Messages
28
Office Version
  1. 2007
Platform
  1. Windows
You could use a named range with something like
Excel Formula:
=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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

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