excel dynamic calendar

[tbuss]

I am trying to create an extremely user friendly excel calendar for displaying information on a digital signage. I am trying to link the days of the month to the dropdown list of month names. Can this be done?

 

[My Aswer Is This]

Sure but we need more details.
In what cell is the drop down?
And what should happen if you choose "March"

 

[tbuss]

I need to be able to click on the drop down and pick a month which is in F1 and the whole sheet changes with it to reflect any changes that are made to each calendar

 

[My Aswer Is This]

That little bit of information tells me hardly nothing.

I need to be able to click on the drop down and pick a month which is in F1 and the whole sheet changes with it to reflect any changes that are made to each calendar

 

[igold]

Check out this post and use a 7x6 grid.

https://www.mrexcel.com/forum/excel-questions/874292-small-7-x-5-monthly-calendar-excel-2010-a.html

 

[Eric W]

There are literally thousands of ways to create a perpetual calendar. So you can see why MAIT is asking for more specifics about what you want. I have half a dozen versions of varying sophistication. Some include holidays, or julian dates, different types of highlighting, spin buttons to select month and year, etc. You can find many of them if you do a Google search for "Free excel perpetual calendar".

The very easiest (well, shortest!) way I know to create a perpetual calendar for a given month is with a single formula. For example:

	A	B	C	D	E	F	G	H
1	November,2017	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2					1	2	3	4
3		5	6	7	8	9	10	11
4		12	13	14	15	16	17	18
5		19	20	21	22	23	24	25
6		26	27	28	29	30
7

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2:H7 {=IFERROR(EXP(LN(IF({1,2,3,4,5,6,7}+{0;7;14;21;28;35}+1-WEEKDAY(A1,1)>DAY(EOMONTH(A1,0)),0,{1,2,3,4,5,6,7}+{0;7;14;21;28;35}+1-WEEKDAY(A1,1)))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the 1st day of the month you want in A1. You need to include the year. I have it formatted here as mmmm,yyyy. Then I added the B1:H1 headings. Then I selected B2:H7. (Any 7 X 6 range will work.) Then I entered the formula in the formula bar, still with the whole range selected, then pressed Control+Shift+Enter.

Now any time you change the date in A1, the calendar changes to match. You can format it as you like, with gridlines or highlighting.

 

[barry houdini]

With Eric's suggested setup this formula will also work

=IFERROR(DAY(TEXT(A1,"yyyy-mm-")&{2,3,4,5,6,7,8}+{0;7;14;21;28;35}-WEEKDAY(A1)),"")

 

[Eric W]

Really nice improvement, Barry!

 

[tbuss]

That little bit of information tells me hardly nothing.

It is a vacation tracking calendar that has available boxes. When a day is available the box is green and when it's not it's red. I need those boxes to stay with the month in the drop down when toggling between months. I already have it changing the dates just can't get it to change the boxes.