Dynamically change the number of days in a month for dropdown

Question

It has to be done without VBA.

I have a list of months to be filled in one cell, say A1. The options can be filled using Data Validation-List (=Months) which causes a dropdown to appear in A1. In cell B1 I enter a year number (for example 2000). Now I want C1 to contain a dropdown, the entries in which change on the basis of the data in A1 and B1. In my example, A1 offers the choices january, february, march, april, may, june, july, august, september, october, november and december.

If I select january or march or may or july or august or october or december (months with 31 days), I should get numbers between 1 and 31 as an option in C1.

If I select april or june or september or november (months with 30 days), I should get numbers between 1 and 30 as an option in C1.

If I select february, I must first check if an year is leap year with this formula:

=((MOD(B1;4)=0)*((MOD(B1;100)<>0)+(MOD(B1;400)=0))=1)

and based on result of this formula(TRUE or FALSE), I should get numbers between 1 and 28 (or 29 if is leap year) as an option in C1.

I have created lists, named Days30, Days31, Days28 and Days29. How can I use Data Validation to switch between those lists for my dropdown in C1?? I'm trying to translate this logic into Data Validation formula, but no luck:

If (A1 = "april" OR A1 = "june" OR A1 = "september" OR A1 = "november") Then 
    Days30
ElseIf (A1 = "february" AND leapyear = 1) Then 
    Days29  
ElseIf (A1 = "february" AND leapyear = 0) Then 
    Days28
Else                
    Days31
End if

Unknown Unknown · Accepted Answer · 2015-08-11T09:40:50

In Z1 put this formula,

=DAY(DATEVALUE(A$1&" "&ROW(1:1)&", "&B$1))
In Z2 put this formula,

=IFERROR(IF(MONTH(DATEVALUE(A$1&" "&ROW(1:1)&", "&B$1))=MONTH(DATEVALUE(A$1&" "&ROW(2:2)&", "&B$1)), DAY(DATEVALUE(A$1&" "&ROW(2:2)&", "&B$1)), ""), "")

... and fill down to Z31.
Go to Formulas ► Defined Names ► Name Manager. When the Name Manager dialog opens, click New.
Give it a name (e.g. lstDOM), leave it as Workbook scope and supply the following for the Refers to:
=Sheet1!$Z$1:INDEX(Sheet1!$Z:$Z, MATCH(1e99, Sheet1!$Z:$Z))
Click OK to create the dynamic named range then Close.
With C1 selected, go to Data ► Data Tools ► Data validation. Choose Allow: List and supply =lstDOM for the Source:.

Your results should resemble the following.

Dynamically change the number of days in a month for dropdown

1 Answers