Need Formula for conditional Formatting

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
I would like a formula for column F, that I can plug into conditional formatting where the lettering turns bold red when there is no date in column G, after it has passed a two week mark. So if we haven't received a response in two weeks, then it is in our face to follow up.

1613758106011.png


Here is the text in the table above, copy and pasted:

Vendor IDVendor NameMerchant(s)Team MemberFirst Processed1.a.
Emailed
Vendor
2.
Received
Response
3.a.
Merch Approval Requested
3.b.
Merch EDI File Approved
4.a.
SNOW Ticket Created
4.b.
SNOW Ticket Number
5.a.
Fixes Completed
StatusNotes
11121GHI IncorporatedMark DoeDoris2/5/20212/9/20212. Awaiting Vendor Response
12345ABC IncorporatedJohn DoeCollin12/21/202012/21/20201/5/20211/6/20211/6/2021N/ARITM01281141/11/20216. Complete
67891DEF IncorporatedJane DoeDoris2/3/20212/10/20212. Awaiting Vendor Response
31415JKL IncorporatedJessica DoeDoris12/22/202012/22/20202. Awaiting Vendor Response
16171MNO IncorporatedLarry DoeDoris12/22/202012/22/20201/13/20212. Awaiting Vendor Response
81920PQR IncorporatedMike DoeCollin12/15/202012/15/20201/13/20212. Awaiting Vendor Response
21222STU IncorporatedJanet DoeCollin2/3/20212/10/20212. Awaiting Vendor Response

Thank you in advance for any assistance you can provide!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Passed two weeks from which date?
The dates in column E?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
If it is column E, then try this CF formula for the entry in cell F2:
Excel Formula:
=AND(G2=0,TODAY()-E2>14)
 

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
So for example: If a cell in column G is blank and todays date is two weeks or more past the date in a cell in column F. I don't want the cell in column f to have bold red lettering if todays date is less than two weeks past.

Does that help?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So for example: If a cell in column G is blank and todays date is two weeks or more past the date in a cell in column F. I don't want the cell in column f to have bold red lettering if todays date is less than two weeks past.
That doesn't seem to match what you showed in your examples.
All the dates in column F that you highlighted in red are less than two weeks old.
 

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
That doesn't seem to match what you showed in your examples.
All the dates in column F that you highlighted in red are less than two weeks old.
Sorry for the inconsistency Joe! I fixed the screen shot to match what I want:

1614033258738.png


Thank you so much for your help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So did you try what I suggested up in post 3?
Did that work for you?
I think it should do what you seem to want.
 

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
No it unfortunately didn't perform how I was hoping, The formula above was also for column E, I changed it to be for column F: =AND(G2=0,TODAY()-F2>14)
and it made cells F4 and F8 Bold red lettering instead of cell F5 and F2 should probably be red now as well. F4 and F8 should should not be red because that is less than two weeks old.

1614101691286.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
OK, just a very slight adjustment.

1. Select cells F2:F8
2. Enter this Conditional Formatting formula:
Rich (BB code):
=AND(G2=0,TODAY()-F2>14)
3. Select your red font formatting option

If you do this, it will match what you show you want up in post 6.
 
Solution

PaigeWarner

New Member
Joined
May 27, 2020
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
OK, just a very slight adjustment.

1. Select cells F2:F8
2. Enter this Conditional Formatting formula:
Rich (BB code):
=AND(G2=0,TODAY()-F2>14)
3. Select your red font formatting option

If you do this, it will match what you show you want up in post 6.
Sorry Joe, never saw this. This works great, thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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