May 05 2022 05:20 PM
Hello,
I need to create a 3-mo calendar (Sun-Sat) that other people can use. The purpose is to show critical deadlines in those 3 months starting on the day the project is initiated. Specifically, the calendar needs to populate 78 days from the first day. So...Day 1 is, say, May 25, 2022. Then - the calendar needs to highlight a key window between 28 days and 42 days after May 25. Then the calendar needs to highlight a final day for completion - 78 days after Day 1. Someone should be able to type in a date (May 25, 2022) into a cell - and then have the whole 3-mo calendar (not inclusive of that cell) populate with the info above, starting with Day 1. It should be super easy for the person who needs to use it.
I've read and watched material online and, unfortunately, not finding good direction. Any suggestions?
Thanks!
Nancy
May 05 2022 06:21 PM
Here's a pic of my idea.
May 06 2022 02:49 AM
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
May 06 2022 12:39 PM
@Hans Vogelaar Sure! Attached.
May 06 2022 01:33 PM
SolutionPlease test carefully.
May 06 2022 01:51 PM
May 07 2022 01:12 PM
Can I ask one follow-up question? I've changed some of the colors, etc. - nothing major. I also added a new tab to calculate the actual 2nd-stage window when the actual date of the 1st-stage is known. In each tab, I notice that the conditional formatting for the 2nd-stage window highlights empty cells if the window involves 2 months - cells that correspond to dates from the previous month above it. The 1st-stage window highlights only the active dates for the full two weeks - none of the inactive cells in the 2nd month. In looking at the conditional formatting, I can't figure out how to make this stop. Any thoughts?
Thank you again - I'm just blown away. You have no idea how helpful this is.
May 07 2022 01:52 PM
Thanks, that's my fault. I created a rule for dates before the start of the 4th month, but forgot to specify No Color as fill color.
See the attached version.
May 07 2022 05:09 PM
This works quite well!! Do I dare ask one more question? I want to highlight in dark blue the close date of the 1st window. That's no problem - I added that rule. However I run into the same problem - the date in the prior month is highlighted in addition to the date in the appropriate month. I've tried different versions of conditional formatting based on your reply above and also tried to replicate the red cell for the 2nd-stage window close. No such luck. Attaching again - asking if you might look one more time. I'm grateful for your expert help.
May 08 2022 01:27 AM
This has to do with the order of the rules in the Conditional Formatting Rules Manager dialog. The rules are evaluated from top to bottom.
The rules that specify "No Color" for cells before the start and after the end of the month should be evaluated first.
You can use the button indicated in the screenshot to move the rule for the close date down.
Result:
Remark: the rule should apply to D6:J11 too.
May 08 2022 02:35 PM
@Hans Vogelaar Thank you! It took me a bit, but I finally figured it out. It now works beautifully. Thank you for all your help.
May 06 2022 01:33 PM
Solution