Solved

# How to do this!

Posted on 2012-09-04
Medium Priority
201 Views
Last Modified: 2012-10-04
How to do this work
Thank you!

0
Question by:dungla2012
5 Comments

LVL 10

Expert Comment

ID: 38363461
0

LVL 43

Expert Comment

ID: 38363563
That was not a very useful title for a question. These titles help the experts to pick questions which they can address best. Also to access the asked questions the titles are the main identification to what could be the content in the question and also help people find questions close to their needs.
0

LVL 14

Accepted Solution

Geisrud earned 2000 total points
ID: 38365189
This link will get you started on using dates to trigger conditional formatting.
http://www.mrexcel.com/articles/due-dates-with-conditional-formatting.php

You may need to modify the instructions a bit to suit your version of Excel.
0

LVL 17

Expert Comment

ID: 38365863
Select the who;e rows from 6 onwards and add a new conditional formatting rule with the formula
``````=IF(AND(\$C\$1-\$B6<7,\$C\$1-\$B6>=0),TRUE,FALSE)
``````
.  The add the formatting you want.
0

LVL 34

Expert Comment

ID: 38367516
Slight tweak to andrewssd3's suggestion, you can use the FLOOR function:

=FLOOR(\$C\$1,7)=\$B6

The conditional formatting is looking for a TRUE or FALSE result, the formula above will give a TRUE or FALSE without needing the IF statement above.

The FLOOR function rounds a number down to the specified factor, in this instance rounding down to the previous mulitple of 7. This is because Excel stores dates as a serial number of number of days since 01/01/1900 with Saturday being the day 7 of each group (week), therefore multiples of 7 will be consecutive Saturdays.

Your sheet above shows a little inconsistency, the day shows Sunday but 9/1/2012 (assuming 1st September) was a Saturday.

Thanks
Rob H
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month17 days, 7 hours left to enroll

#### 830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.