# Premium Excel Course Now Available!

## How to auto insert dates based on selection of drop down menu

Formulas, Functions, Formatting, Charts, Data Analysis, etc.
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

### How to auto insert dates based on selection of drop down menu

Hello,

I'm clearly not an expert when it comes to Excel so, I'd appreciate if anyone could help me out with this.

I'm trying to come up with a drop-down menu containing different options (obviously) and when selecting either one of these options a list of dates will show up. So, let's say I select option A, then certain rows in my Excel document will automatically be filled with the dates of all Sundays of a specific month. If I select option B, the dates of all Saturdays of a specific month will appear. I have a different tab for each month of the year so, the formula can be adapted accordingly.

I hope you all understand what I mean. Is this doable?
• 0

Oculi83
Rookie

Posts: 1
Joined: Nov 3, 2016
Reputation: 0
Excel Version: 2016

### Re: How to auto insert dates based on selection of drop down menu

Hello Oculi, There are a few different ways you can go varying from extremely easy to much more complex depending on how you sheets are laid out.
Can you supply a sample sheet showing how your calendar is set up please?
Also, do you just want a list of dates (either Sat or Sun) or do you want those dates highlighted within your calendar as well?
• 0

Beamer
Excel Junkie

Posts: 217
Joined: Oct 3, 2014
Location: New South Wales, Australia
Reputation: 31
Excel Version: 2010

### Re: How to auto insert dates based on selection of drop down menu

Hello sir.

Cell A1 have a list of Months
if A1 = Jan-17
Then COlomn D will display list of saturdays
D5, copy this formula =IF(WEEKDAY(EOMONTH(A1,-1)+1)=7,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(7,7))-WEEKDAY(EOMONTH(A1,-1)+1))

THen in D6 add 7 to D5, and so on for all saturdays
Similarly in Colomn E will display all sundays
E5 copy this formula, =IF(WEEKDAY(EOMONTH(A1,-1)+1)=1,EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+(8 + MOD(1,7))-WEEKDAY(EOMONTH(A1,-1)+1))

THen add 7 tp E5 in E6, and so on till you have list of all sundays

I think this is the solution for your issue

Regards
Shashank
• 0

[b][color=#400040]Shashank[/color][/b]
Experience is what you get when you donâ€™t get what you want.
Shashank
Valued Contributor

Posts: 126
Joined: Jul 29, 2013
Location: India
Reputation: 10
Excel Version: 2007