CODEWITHSUNDEEP
excelexcel-formula
Mr.Light
Mr.Light

Reputation: 11

Highlight a cell (weekly) based on the start and end date

I'm trying to build a project planning doc that automatically highlights the weekly cells based on the start and end dates of the project.

----------
Start    End      W1       W2       W3       W4       W5       W6       W7
----------
Date     Date     03/29/14 04/05/14 04/12/14 04/19/14 04/26/14 05/03/14 05/10/14
----------
04/04/14 05/09/14

03/30/14 04/11/14

Based on the Start & End date, the respective Weekly should highlight.

Upvotes: 0

Views: 1542

Answers (1)

John Bustos
John Bustos

Reputation: 19564

You can accomplish this using conditional formatting.

Suppose you start your sheet in cell A1. That would mean that your week values would be in cells C1 (=3/29/14), D1 (=4/5/14), etc and your first start date would be in A2 (=4/4/14) and end date in B2 (=5/9/14).

That being the case, highlight your desired area to be formatted (starting in cell C2) and go to:

conditional formatting > New Rule > Use a formula to determine which cells to format

and use the following formula:

=AND(C$1<=$B2,D$1>=$A2)

(or play around with the <= and >= to suit how you choose where a week ends / starts) Then select how you want the cells to be formatted if they meet your criteria and that should do the trick.

Hope this helps!!

Upvotes: 0

Related Questions