Monthly Calendar using Beginning and End Dates

I put a calendar file together recently in Post #7 of this thread:

https://www.excelforum.com/excel-gen...ates-data.html

although the OP never got back to me about it.

This one allows you to select one of three fields to display using a drop-down, and it can display up to 10 events for each day on the calendar in ranges of 10 (also chosen from a drop-down), so effectively it can show up to 100 events per day, in groups of 10. . The month and year can also be chosen from drop-downs (the yellow cells in column K.

Although this file only caters for single dates, I have done others where a date span can be used (start and end date), and formulae expand the raw data into daily data and can then be used in a similar way. The example in this thread:

https://www.excelforum.com/excel-for...anges-for.html

shows how that can be done (with the blue formulae).

Hope this helps.

Pete

Pete,

Thank you for the reply. I definitely need to be able to at least start with a date range, since I don't want the people using this to have to add the same info for every day (which could be 60 times if it is for 2 months) but I think your second option could be possible if I just put it onto a different tab and hide it. I just need to figure out how to also get it to show the words that I want. I will play around with it for a bit and let you know how it goes!

Does this:

...Demand Unit, the Sub-Demand Unit and the Action...

mean that you want to see all three fields at the same time?

Pete

I have put a calendar together for you which does what you want, i.e. takes a date range and displays every day within that range.

Before I submit it, though, please indicate that you have read this post - otherwise you will not get an automatic notification that someone has posted to your thread and so you might miss it or ignore it.

Pete

Hi Pete,

Sorry for the delay! I couldn't access the forum yesterday for some reason... I was able to figure something out, but I would love to see the solution you put together and see if it works better than mine!

You can see in the first tab of the attached file that I have put your data table in columns I to P. I've made a slight change to this - you had a hidden column which concatenated the first two fields together, but I couldn't see a need for this so I have deleted it. Instead, I have a fourth column which concatenates the first 3 columns together. You can hide this column if you wish.

To the left of the data table are formulae which expand the date ranges from your table so that there is one record for each day of the range. These formulae have been copied down to row 1000, in order to accommodate more data being added to your table. You can see from cell B12, however, that the 10 entries from your table generate over 450 individual dates, so you might want to copy the formulae down much further. Actually, the first two columns do not need to be copied as far (just enough to cover your data), and you might want to move these to be part of your data table. Column A is the equivalent of your column P, anyway, although I've included a few extra checks, and column B is just a cumulative number of days - the zero in B2 is important, though, for other formulae.

The columns with my formulae in can be hidden if you want the sheet to look like the one you submitted the other day.

In the Calendar sheet I display a monthly calendar with Sunday to Saturday across the sheet. If you do not want to see the weekends, then you should hide the appropriate column - do not delete the columns, as this will mess up the formulae. You can select the month and year to display using the data validation drop-downs in cells K5 and K6, and you will see the day representing the first of the chosen month (and obviously the layout for that month) change immediately.

I have set up the capability to display up to 10 events per day, and the drop-down in cell K10 allows you to select different groups of 10 (i.e. 1 to 10, 11 to 20 etc.), so effectively you can display up to 100 different events per day, though obviously not all at the same time. Judging by your sample data, this might not be something that is important to you, but the facility is there. There is another drop-down in cell K15 which controls what to display - you can choose one from Demand Unit, Sub-Demand Unit, or Activity, but you can also choose to display All three fields together. You will see on the third tab that 2 extra named ranges have been added to control these last two drop-downs. The column widths have been set wide enough to display all the data in one line, so there is no need for wrapping the text.

You will see in making any changes to the drop-downs, that the display adjusts automatically. The calendar is purely formula-driven, so there is no need to enable macros, and the display will change immediately following any change in the drop-downs.

So, a few initial things to do like hiding columns that you don't want to see, and then to use the file you just put data in the first sheet and then use the drop-downs in the calendar sheet to control what you see.

Hope this helps.

Pete

Pete,

This is quite cool! Thank you so much for your help!

You're welcome - thanks for the rep.

Pete