Help with Autopopulating List into Calendar

What form would you like the calendar to take? A common one would be month-to-view, with days of the week arranged across from, say, Sunday to Saturday, and then 4, 5 or 6 weeks below. Looking at your data, I can see that you can have multiple "events" on a single day, so you need to be able to cater for the maximum that could occur (which is ?)

I have devised many calendar files that do this, where the month and year can be selected from drop-downs and the data is displayed automatically to suit those choices. The only concern I have is the amount of data that you would like to be displayed on the calendar, as it could become quite cramped.

Hope this helps.

Pete

Hi Pete,

Thanks for the reply!

Specifically, I would like the calendar to take the form of of month-to-view, with the week starting on Sunday and ending Saturday, with the entire month viewable in one view (just the way a traditional calendar looks). I think maximum "events" on a single day would be best at 8 if possible (minimum 6 if not possible). Also, in terms of cramped info, if needed I only really need the calendar to display the assignment column on the correct date with status (either COMPLETE, WORKING, TO START, KEEP FREE, but status can also just be color coded if that is easier), the rest can be dropped if needed. If space allows, I would also like to have assignor, but if that is too cramped that does not need to be in the calendar view.

I really appreciate your help on this!

Cheers

Will you just have one date for each event, or are you likely to want to put in a start date and an end date for events which span more than one day?

Pete

Hi Pete,

Just one date is preferred, since it is just due dates and not ever date ranges.

Thank you!

See if the attached file is what you are after. I've copied your data into the Assignments tab, from column D onwards. I also have formulae in columns A and B which prepare your data for display on the calendar. The formula in cell B2 controls what is displayed:

=IF(OR(A2="",A2="-"),"",H2&" - "&J2)

in this case it is column H (Assignment) and column J (Status) with a hyphen in between, but it is easy to change the section in red to something else to suit your needs, and then copy down. The formulae have been copied down to row 1000, but you can copy them further if you wish. You can also hide columns A to C to help protect them from accidental changes. Note that your assignment list can be entered into the table in any order - they do not need to entered chronologically.

On the Calendar sheet you can choose the month and year to be displayed in cells K5 and K6, and the display will automatically update. You can have up to 8 different "events" on any one day, and an alternating colour will emphasise the events. The calendar shows "orphan" days on the top rows. These occur whenever a 30-day month starts on a Saturday or a 31-day month starts on either Friday or Saturday, as the final 1 or two days would need a sixth week to complete the display. To avoid this sixth week, those days (which will always be a Sunday (plus maybe a Monday) are shown at the top of the calendar with a different coloured heading for clarity. See, for example, January 2021.

I think the conditional formatting (which turns the lines on and off at the end of each month) needs a bit of tweaking, but I don't have time to look at that just now.

Let me know how you get on playing about with it.

Hope this helps.

Pete

P.S. You can use it to display any data, not just Assignments. You could add public holidays to your data table, or any data which is related to a date and would benefit from being displayed on a calendar.

Thank you! This is exactly what i had in mind! You are amazing!

Glad to help, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete