Calculating dates and conditional formatting

[PP299]

Please help!!

I am looking to create a formula in my spreadsheet which will calculate some dates for me firstly I want to calculate a formula from a start date plus 6 weeks and then from there format the cells so if that date goes two days over it turns amber then red if 3 days over? does that make sense?

I am getting nowhere fast but i'm not an expert on different formulas unfortunately.

Cheers all

 

[GCM]

To calculate a date that is 6 weeks after the start date, just add 42 to the start date. So if your start date is in A1, the formula in B1, to show the date 6 weeks later is

=A1+42

Next, to highlight when the current date is 3 days or 2 days after the '6 week later' date, use conditional formatting. Highlight the cells to be formatted (starting with cell B1), enter conditional formatting, choose to use a formula and enter:

=today()-B1>2

and choose a red background for the formatting.

With the cells still highlighted, re-enter the conditional formatting and add a second rule. In the formula field enter:

=today()-B1=2

and choose an amber background colour for the formatting.


Of course you can omit the '+ 6 weeks' column completely and for the conditional formatting, just use the following formulas on your original dates:

=today()-A1>44

and

=today()-A1=44

I hope this helps