# Calculating dates and conditional formatting

#### PP299

##### New Member

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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### GCM

##### New Member
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

Replies
7
Views
599
Replies
1
Views
671
Replies
2
Views
351
Replies
3
Views
497
Replies
4
Views
283

1,190,613
Messages
5,981,928
Members
439,743
Latest member
KatieO

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back