Struggling with conditional formatting formula for a date calculation

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
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.

WhitoniaWhitchampion
Date of next SIRE inspection16/08/1627/04/16
Date of pre-inspection19/07/1630/03/16

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,382
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Whitonia</td><td style=";">Whitchampion</td><td style="text-align: right;;"></td><td style=";">CF Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Date of next SIRE inspection</td><td style="text-align: right;;">16/08/16</td><td style="text-align: right;;">27/04/16</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Date of pre-inspection</td><td style="text-align: right;;">19/07/16</td><td style="text-align: right;;">30/03/16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=AND(<font color="Blue">B3=TODAY(<font color="Red"></font>),B3+28>=B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
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

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

Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,382
Office Version
  1. 2016
Platform
  1. Windows
Oops, Sorry, misread your post, try this:

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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Whitonia</td><td style=";">Whitchampion</td><td style="text-align: right;;"></td><td style=";">CF Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Date of next SIRE inspection</td><td style="text-align: right;;">16/08/16</td><td style="text-align: right;;">27/04/16</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Date of pre-inspection</td><td style="text-align: right;;">19/07/16</td><td style="text-align: right;;">30/03/16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=AND(<font color="Blue">B3<=TODAY(<font color="Red"></font>),B3+28>=B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,652
Messages
5,597,373
Members
414,139
Latest member
okela0

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top