Populate Excel Calendar from Columns

Hi Excel boffins
For all my many sins I became involved in volunteer work that is taking a lot out of me, but I guess that's what I signed up for.
I searched the forum and the closest to a solution I found is this decade-old thread: http://www.excelforum.com/excel-prog...-calendar.html
As per my attached example, I have to show visits to our three sites on a calendar, including who the rep is going to the site.
I tried using a simple Gantt-type calendar, but it just doesn't work.
So, what I'd like:
1. An "intelleigent calender that will populate when I cnage the year in L1 to the next year.
2. Populate the calendar based on the dates in Visits, adding the Rep's name. Since departures (generally) happen early the departing day is not reflected in the calendar. (simple workaround if the rep departs late).
3. The fill is easy to do with conditional formatting I know.
4. We have a number of special non-working days indicated in the Visits sheet - highlighting the day will be a great feature.
Waiting in anticipation for your valued contributions!

Do you only have 3 sites, or have you reduced the file for demonstration purposes?

Instead of having a group of cells for each site, and for this to be repeated across, it would be easier to have one extra column (in addition to Arr, Dep and Rep in the Visits sheets) in which to record the Site.

Do you want the starting month to be variable (e.g. selected from a drop-down), or do you want to show all the months for the year?

For the calendar to respond to the year in cell L1, you will need to generate the dates automatically for this (a formula can do this).

Pete

Something like this should work I hope?

Originally Posted by antonf

... I need to show the rep that will be visiting the site on the calendar as well ...

Yes, I didn't understand your quote from above, as it looks like you are wanting to show different sites on different rows of the calendar (and colour-coded), so I wasn't sure what you meant.

I'll have a go at putting the calendar together for you, but do you have further examples of the holidays to be avoided? Presumably most of these can be calculated for each year, so they can adjust automatically depending on the year chosen in cell L1.

Pete

Yes, Id appreciate each site/project to have it's own row as overlaps can become messy as in March.
Some SNWD are fixed, some change (religuous) and the organisation for various reasons "block out" visits on certain dates or over certain periods. I presume a range for them would be the simplest solution?

Thanks a lot!

I was just thinking that if you changed the year in L1 on the Calendar sheet, then you would like to take account of the SNWD dates for that year, rather than the fixed dates that you show in your example file. One way round it would be to have a list of dates for every year.

I'm still working on automating the calendar part, and then I can tackle displaying the data - it's not really a 5- or 10-minute job, this.

Pete

Take a look at the attached file - I think it does everything that you've asked for.

Hope this helps.

Pete

A range for SNWDs would be perfect!
A friend had such a calendar - asked her about it, she's looking.

Thanks a lot, will have a look in an hour or so!

Absolutely PERFECT, thanks! The only thing, the calendar shows the departing day, I need to show the last day the rep is working with the staff, so departing day -1. If the rep leaves late, I can change the date.

You're a WIZARD!!!

Originally Posted by antonf

... I need to show the last day the rep is working with the staff, so departing day -1 ...

You can remove the 1+ in the formula in cell A4 of the Visits sheet, to leave you with this:

=IF(I4="",0,IF(J4="",1,J4-I4))

and then copy the formula down. This will exclude the date in the Dep column from the displays.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark 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).

Hope this helps.

Pete

Thank you very much Pete!
Working with people who are, to be polite, are computer illiterate is a serious challenge, but seeing them slowly learn some basic things is extremely rewarding at the same time.
I can't wait to show the lady who has to enter this how it works - I can promise you it will be like a magician's show!
Thanks once again!

Compliments of the season Pete, and a hope 2024 is a great year!

I was about to send out the calendar for the first half of 2024 when I noticed something peculiar - 1 Jan 2024 is next Tuesday, not Monday! Your assistance will be highly appreciated!

Well spotted. You need to amend the formula in cell C4 of the Calendar sheet to this:

=IF(B4<>"",B4+1,IF(WEEKDAY(DATE($L$1,1,1))<>COLUMNS($B:C),"",DATE($L$1,1,1)))

i.e. change the B that was there to a C. Then you can copy the formula from C4 across into D4:G4, and then save the file. I've attached it here for you.

Hope this helps, and happy New Year.

Pete

Much appreciated Pete!!