Struggling with conditional formatting formula for a date calculation

[matthewoaks]

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>

 

[jtakw]

Hi,

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

Excel 2010
	A	B	C	D	E
1		Whitonia	Whitchampion		CF Formula
2	Date of next SIRE inspection	16/08/16	27/04/16		FALSE
3	Date of pre-inspection	19/07/16	30/03/16
Sheet1

Cell Formulas
Range	Formula
E2	=AND(B3=TODAY(),B3+28>=B2)

 

[matthewoaks]

Hi,

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

Excel 2010

	A	B	C	D	E
1		Whitonia	Whitchampion		CF Formula
2	Date of next SIRE inspection	16/08/16	27/04/16		FALSE
3	Date of pre-inspection	19/07/16	30/03/16

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =AND(B3=TODAY(),B3+28>=B2) <thead> </thead><tbody> </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

 

[jtakw]

Oops, Sorry, misread your post, try this:

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

Excel 2010
	A	B	C	D	E
1		Whitonia	Whitchampion		CF Formula
2	Date of next SIRE inspection	16/08/16	27/04/16		FALSE
3	Date of pre-inspection	19/07/16	30/03/16
Sheet1

Cell Formulas
Range	Formula
E2	=AND(B3<=TODAY(),B3+28>=B2)