New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

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

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

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

Postby Oculi83 » Thu Nov 03, 2016 3:48 am

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

Postby Beamer » Tue Nov 08, 2016 12:56 am

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

Postby Shashank » Wed Feb 08, 2017 9:56 pm

Hello sir.
i think the following will address your issue

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


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 76 guests

cron