[CF] Highlighting blank columns based on a date in a different cell on a full calendar.

Hi Jeff,

Thank you very much for the response and apologies for the insufficient information.
I work in events, and everytime we do an event we first make a retro-planning and work the dates backwards to see how far in advance we need to start so that we can get the things ready in time.
The timings of the clients coming to us and letting us know is unpredictable, sometimes they come to us 1year in advance and sometimes just 2months or even 1 month before they day of their desired event day.

Depending on the scale of the event and timing of when the client comes to us, I make a new retro-planning sheet each time for the period we have right till the event.
(For example, the client comes to us in Feb saying that they want to do an event in Nov, and the scale of the event requires around 3months of preparation in advance.
So I create a retro-planning sheet just on the months of Sep, Oct and Nov)
But this time, i want to create a MASTER retro-planning excel sheet where I can just duplicate the sheet to adjust to the designated event and take out unnecessary "Months" of the calendar.

Please see below attached my current excel.

Retro Planning Template 2024.xlsm

Yes, 3 colors. I have added the last color "Pinkish red" for TAKEDOWN.
Thank you very much for the solution as well, understood!

Current my row 5 are just numbers and not date values.
Would it be possible to change them into date values but still keep them seen as numbers? (1,2,3,4,5 etc)

----------------------------------------------------------------------------------------------------------------------------
*Additionally, as I would like to use this same template for the years to come. (2025, 2026, 2027 ... etc)
I have tweaked the "Edit Formatting Rule" to the below,
=$H$1=DATE(YEAR(TODAY()),1,31)

I assume row 5 would also need to have the same rules (YEAR(TODAY()) as well correct?

Hi Jeff,

Good afternoon from Japan.
Thank you very much for the revised CF! Learned a new thing today.

Also appreciated for pointing out the highlighting of weekends and holidays, that would really save me so much time!
Would it be possible to do this based on specifically Japan's Holidays?
Sorry as I am a beginner and am keen on learning, would you also kindly explain the formula for the weekend / Holiday as well?
I was searching through the internet for answers and could only come upon the below,

For weekend formula, it would be

"=OR(WEEKDAY(C5)=7,WEEKDAY(C5)=1)" correct?
Couldnt figure out how to link it to C5. Or would it be easier if I link it to C6?


Sorry about that as I forgot to mention that there will be multiple days for each category depending on the scale of the event.
Yes I would also love to have the in-betweens highlighted as well.

Please find attached cleaned-up version.
I have also added a new tab, as the year 2024 has Feb 29th.

MosesLimLA=MASTER_Retro Planning Template1013.xlsm


Once again thank you so much and looking forward to your response!

Hi Jeff,

Good afternoon from Japan, and hope you had a great weekend.
Apologies for the back-to-back replies.

I have proceeded to add the CF for
- "Highlighting weekend" CF using =WEEKDAY
= "Highlighting holidays" CF using =OR

Please find attached updated version and was wondering if you could spare a few minutes to look at it and let me know if this would work for my excel.

(TEST) MASTER_Retro Planning Template copy.xlsm

Thank you very much and looking forward to hearing from you!

Best,

Hi Jeff,

Good morning from Japan.
Thank you very much for the revisions! Now this is way more convenient and time-saving than what I was expecting!
Understood regarding "S" for weekends, holiday tab with formula & explanation.
Also appreciated on the vertical line, was also thinking whether if there was a way to automate that and you've done for me. Super thankful!

All cleared for this thread! Thank you!