Highlight row of current data range and calculate target to date

Question

How would I use conditional formatting to highlight a row where the value in column C falls within a certain date range (i.e. this present week).

For example in this current example, week 4 would be highlighted (because today's date falls in between 21 Mar and 28 Mar.

Secondly how I would I add up all the values in B to date for cell B12?

For example in this case the value would include all the values in cell B up until 21 March (last weeks date) - so 745.

Answer 1

Conditional Formatting:

You could use the following in your formula box when you create a conditional format and apply this to the range A3:C10. :

=AND($C3>TODAY(),$C3-7<TODAY())

Sum to date:

=SUMIF(C3:C10,"<"&TODAY(),B3:B10)