0

Hello cordial greetings

I am a bit confused, I need to perform a data validation in cell C7, so that the days correspond to the month set in cell C6 and the year found in cell C8. The data is recorded in the DATA tab; I have tried INDEX + MATCH functions, as well as OFFSET, but none of them meet what I require (I think I am making a lot of mistakes). Can you please help me with a solution or give me a hint on how to find a formula that does what is required.

Exactly, what I am trying to do is that when selecting for example the month of January (C6) of the year 2018 (C8), in the drop-down list of the cell C7 only the 31 days corresponding to the specified month appear. That is to say, if I select February, 28 days will appear and not 30 or 31 days, with the exception of if it is a leap year, in which case, 29 days should appear for the month of February.

To do this, I want to select the data corresponding to Year (A5:A45388), Name (C5:C45388) and Day (D5:D45388), which are located in the "Data" tab, and depending on the year selected in the Sheet 1 tab (C8) and month (C6), bring me the respective days for that date.

Since January, March, May, July, August, October and December have 31 days, if I select any of these months, regardless of the year, those 31 days should appear in the drop-down list located in cell C7, and not 30 or 28 days.

The same would apply if you select the months of April, June, September and November, in which case the 30 days should appear in the drop-down list in cell C7 and not 31 or 28 (29 if February is part of a leap year).

Picture.1

Data

Thank you very much for your valuable attention.

PS: I have assigned range names to the table found in the DATA tab from the values in the top row (column header) and I am using Excel 365. In case you require it, a copy of the book with the data presented in the images is available for download in the link.

worksheet

Improve this question
4
  • What days should correspond to a month and year? It's unclear what you want. December has 31 days... which one would you like? Where should the output be and based on what? Please provide the exact formulas you have tried, it's output and the expected output.
    – cybernetic.nomad
    Commented Aug 4, 2021 at 19:15
  • I will edit the question
    – Camila Sanchez
    Commented Aug 4, 2021 at 21:23
  • Is there any other purpose for selecting the date? Seems like all you would need is the month and year to return the data your desired data. Having said that use the =EOMONTH() formula to get the last day of a particular date value...
    – gns100
    Commented Aug 4, 2021 at 21:50
  • Yes, the days are necessary, since for certain dates there is no data to be searched.
    – Camila Sanchez
    Commented Aug 4, 2021 at 22:08

1 Answer 1

Reset to default
1

If you just want to calculate the number of days in a month based on the year in cell C8 and the month in cell C6, I suggest you try this formula:

=DAY(EOMONTH(DATE(C4,C2,1),0))

enter image description here

Besides, if you want to select the corresponding data in the Data worksheet, I suggest you try this:

=COUNTIFS(Data!A5:A63,Test!C8,Data!C5:C63,Test!C6)

enter image description here

Data worksheet:
enter image description here

Update： Please check if the following is helpful to you.

enter image description here

Improve this answer
7
  • What about days, months and years before 01/01/1900?
    – Reddy Lutonadio
    Commented Aug 5, 2021 at 13:19
  • Dear @Viki Ji thank you very much for your valuable collaboration, unfortunately this is not what I am looking for,
    – Camila Sanchez
    Commented Aug 5, 2021 at 21:45
  • @Reddy Lutonadio, the amount of data is huge, I just gave a simple example.
    – Viki Ji
    Commented Aug 6, 2021 at 1:32
  • @CamilaSanchez What are you then looking for (as the 1st formula works for most of your data)?
    – Reddy Lutonadio
    Commented Aug 6, 2021 at 5:01
  • @Reddy Lutonadio, what I was looking for was that when selecting the month, for example February, in cell C7 I would see the days corresponding to that month in a drop-down list (from 1 to 28 or 29 days if it is a leap year), likewise, if I chose the month of March, regardless of the year, I would see in cell C7, the days that month has, that is from 1 to 31. And so with each of the months, that would bring me the days that each month has.
    – Camila Sanchez
    Commented Aug 6, 2021 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .