Saturday, April 1, 2017

yogi_Conditionally Format Cells B3:3 If The End Date (in cell B3) Fell Within Weeks 1 To 52 of The Year of The End Date

Google Spreadsheet   Post  #2141
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-01-2017
question by LobsterSlapper:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1EmzRio1SNw;context-place=forum/docs
Week-based Gantt Chart:solution for "shared" weeks?
Does anyone have suggestions for how to handle "shared" weeks, for a weekly Gantt Chart? 

My current spreadsheet uses conditional formatting (=WEEKNUM($A3)>=(B$2)) and cells based on week numbers, to display a timeline of when a project is scheduled to end. An end date is entered in A3, and the corresponding weekly cells are highlighted .

This works for most weeks, but some weeks are "shared" between months. Ex. 12/1/2017 is the first Friday of December, but using the WEEKNUM function and my current setup, this counts as the last week of November, so the cell for the first week of December (AX3) is not highlighted. For those types of dates, I need to highlight the following week also. 

Does anyone have suggestions on how to resolve this? This is part of a bigger worksheet that is otherwise working as expected, so I'd rather not make a huge change.


The following are the dates that fall into the previous week:
Feb 1-4 
March 1-4 
April 1 
May 1-6 
June 1-3 
July 1 
Aug 1-5 
Sept 1-2 
Nov 1-4 

Dec 1-2