having trouble with building a dropdown box that references days in a year and days in qtr

Hey guys,

I could very much use everyone's help. So I'm creating a dropdown box (Which I already know how to make, using data validation) for my spreadsheet that contain years from 2010 to 2020 in D5 (referenced from sheet2) and coressponding days in a year in E5 (referenced from sheet 2 using Vlook up). In F5, I have another dropdown box for each quarter: 1st, 2nd, 3rd and 4th (referenced from sheet 2) and days in quarter in cell G5: 90, 91, 92, 92 (using Vlook up from sheet 2).
Now hard part, the days in year E5 has to take into account the leap year (366 days in 2012, 2016 and 2020) which I managed to do also using the Vlookup. but for the the days in quarters (G5), I cant get the formula to factor in extra day in 1st quarter for the leap year. How can I resolve this? I uploaded whatever work i have done so far
Maybe using 2 IF formulas in G5 (days in qtr)?

You should be using the end of month function. EOMONTH

This will give you the serial number for the last day of each month.

Do that for each month in the quarter and add then together.

Hello,

I used vba for your drop down validation lists and used an if related formula for your quarters based on the days figured for each quarter (which I determined on your sheet2). Not sure this is exactly what you were looking for, but might be an alternative. Good luck!