# Struggling with conditional formatting formula for a date calculation

#### matthewoaks

##### New Member
Hi All,
There is probably a really simple formula for this but can't get my head around it.
I have a spreadsheet which I need to do bit of conditional formatting on.
I have a basic table, ships names along the top, types of inspection along the side
In the boxes are the dates when the inspections are due to happen.
What happens is we have a pre-inspection approx 4 weeks prior to the SIRE inspection to make sure everything is OK and if any items need doing before the actual inspection these can be closed out. Simple!
What I want is some conditional formatting in the Date of pre-inspection to highlight the cell in yellow when the inspection is coming up. This should be highlighted 4 weeks prior to the SIRE date. For example If we look at Whitonia...If todays date was the 19/07/16 then the pre-inspection date would be yellow, all the way up until the SIRE inspection date. Hope this makes sense.

 Whitonia Whitchampion Date of next SIRE inspection 16/08/16 27/04/16 Date of pre-inspection 19/07/16 30/03/16

<tbody>
</tbody>

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,

Use the E2 formula in my sample as your Conditional Formatting formula:

Excel 2010
ABCDE
1WhitoniaWhitchampionCF Formula
2Date of next SIRE inspection16/08/1627/04/16FALSE
3Date of pre-inspection19/07/1630/03/16
Sheet1
Cell Formulas
RangeFormula
E2=AND(B3=TODAY(),B3+28>=B2)

Hi,

Use the E2 formula in my sample as your Conditional Formatting formula:

Excel 2010
ABCDE
1WhitoniaWhitchampionCF Formula
2Date of next SIRE inspection16/08/1627/04/16FALSE
3Date of pre-inspection19/07/1630/03/16

</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=AND(B3=TODAY(),B3+28>=B2)

</tbody>

<tbody>
</tbody>
hi and thanks for the assistance. I've just inputted this formula and tried it, it works great when the date in B3 is exactly 28 days before B2, however is there a way for this cell to stay yellow 28 days before and all the way up to the SIRE date until the SIRE date has been changed.
Thanks again for the help

Use the E2 formula in my sample as your Conditional Formatting formula:

Excel 2010
ABCDE
1WhitoniaWhitchampionCF Formula
2Date of next SIRE inspection16/08/1627/04/16FALSE
3Date of pre-inspection19/07/1630/03/16
Sheet1
Cell Formulas
RangeFormula
E2=AND(B3<=TODAY(),B3+28>=B2)

Replies
3
Views
121
Replies
3
Views
217
Replies
0
Views
383
Replies
1
Views
812
Replies
2
Views
313

1,203,460
Messages
6,055,556
Members
444,797
Latest member
18ecooley

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