Well it was time to break out the mad crazy Excel skills (actually it’s quite easy to do but let’s keep that secret to ourselves).
I first created a list of project activities with start and end dates. Then I created a “calendar” with the start date and end date with increments of 7 days. Make sure to format the calendar as short date.
Now, whenever a day falls between start and end day for a corresponding activity, I want to highlight that cell so I first need to identify if the calendar date falls between the start and end date.
I used the $ sign in the formula to lock the calendar date. Next I just copy the formula into all the calendar cells (D4 through AG13).