Formula To highlight dates due within six months of the date

A

AndrewMartin1

New Member
Joined
Jun 27, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm a complete novice when it comes to Excel and don't know anything unless i can find it on forums i have 3 excel columns that have dates in and i want to use a formula that will highlight dates that are going to be due in six months of the date in the box. i have no idea how or if this can be done? the columns run from F1 and G1 to F96 and G96

SIA RenewalCCTV Renewal
20/12/2024N/A
N/A
N/A
N/A
29/06/2026N/A
12/12/2023N/A
07/04/2025N/A
01/03/2025N/A
N/A
26/05/2025N/A
12/02/2026N/A
20/09/2023N/A
04/09/2025N/A
10/09/2023N/A
15/03/2024
06/02/2026N/A
14/08/2024N/A
12/06/2025N/A
17/07/2023N/A
15/03/2025N/A
28/08/202329/03/2025
28/07/202414/10/2024
02/12/2025N/A
05/07/2026N/A
18/12/202322/07/2024
25/11/2023
27/09/202410/12/2024
11/07/2023N/A
14/01/2024N/A
15/10/2023N/A
24/07/2025N/A
07/03/202601/10/2023
25/04/2025N/A
16/09/2023N/A
12/02/2025N/A
31/03/2026N/A
27/08/2026N/A
19/03/2024N/A
31/03/2025N/A
17/02/202610/02/2026
16/11/2025N/A
03/07/2023N/A
01/05/202609/05/2026
13/01/2024N/A
11/10/2025N/A
29/08/2025N/A
01/11/2025N/A
21/08/2024N/A
16/03/2024N/A
26/03/202429/08/2025
20/08/2025N/A
20/03/2026N/A
30/06/2025N/A
29/09/202507/08/2025
05/07/2023N/A
16/01/2026N/A
24/10/202528/09/2024
10/04/2024N/A
21/06/2026N/A
12/10/2024N/A
13/07/2023N/A
04/06/2026N/A
25/05/2024N/A
17/04/2024N/A
28/03/2026N/A
15/05/2025N/A
25/05/2024N/A
01/03/202527/05/2025
21/09/202408/10/2025
03/07/202517/01/2026
03/10/202325/11/2024
09/07/202612/10/2024
16/09/2023N/A
25/08/202401/01/2025
22/11/202514/04/2024
29/03/2025N/A
09/10/202530/05/2026
14/02/2024N/A
03/10/2024N/A
28/10/2023N/A
18/10/2025N/A
02/11/2025N/A
13/10/2024N/A
24/10/2024N/A
20/10/2024N/A
16/05/202520/11/2025
09/03/2024N/A
01/03/2026N/A
01/07/2026N/A
02/04/2024N/A
14/10/2023N/A
11/11/2024N/A
24/01/2026N/A
02/03/2026N/A
14/02/2025N/A
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi @AndrewMartin1. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following formula in a conditional format.
Applies to cells from F2 to G100.

Dante Amor
FG
1SIA RenewalCCTV Renewal
220/12/2024N/A
3N/A
4N/A
5N/A
629/06/2026N/A
712/12/2023N/A
807/04/2025N/A
901/03/2025N/A
10N/A
1126/05/2025N/A
1212/02/2026N/A
1320/09/2023N/A
1404/09/2025N/A
1510/09/2023N/A
1615/03/2024
S1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:G1000Expression=AND(F2<>"",F2<(TODAY()+180))textNO


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
DanteAmor said:
Hi @AndrewMartin1. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following formula in a conditional format.
Applies to cells from F2 to G100.

Dante Amor
FG
1SIA RenewalCCTV Renewal
220/12/2024N/A
3N/A
4N/A
5N/A
629/06/2026N/A
712/12/2023N/A
807/04/2025N/A
901/03/2025N/A
10N/A
1126/05/2025N/A
1212/02/2026N/A
1320/09/2023N/A
1404/09/2025N/A
1510/09/2023N/A
1615/03/2024
S1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:G1000Expression=AND(F2<>"",F2<(TODAY()+180))textNO


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
Click to expand...
Hi Dante.
thank you for the quick response and the warm welcome, where would I enter the formula in the conditional formatting section to get it how you did. I tried under the manage rules section. its worked but more that six months is highlighting
 

Attachments

  • Capture.PNG
    Capture.PNG
    118.1 KB · Views: 10
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
212
POLACOLOCO
P
C
  • Question
SUMIFS if a date is within the range
Replies
1
Views
198
Fluff
Fluff
M
  • Solved
How to count certain month from a column
Replies
3
Views
115
Joe4
Joe4
R
  • Solved
Allocate between dates to months then summarise by month and year
Replies
7
Views
265
Eric W
Eric W
S
  • Solved
Find Text in a cell
Replies
5
Views
148
Fluff
Fluff

Forum statistics

Threads
1,215,089
Messages
6,123,058
Members
449,091
Latest member
ikke

Share this page

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
Back
Top