Formulas and Conditional Formatting Using Dates

Mel2023

New Member
Joined
Jan 30, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Help! I really hope you can respond to this and help me!

I need a formula(s) to apply conditional formatting based on various dates in a column both on the same tab and from a previous tab in the same workbook. Column A is "Date Received" (where the date will vary by when something was received). Column B would be Date Created.
The basics of the formula I need would be if Column A date is greater than 1 week (or 7 days, whichever is easier), format the cell yellow, if greater than 2 weeks (14 days), format the cell red.

Specific Example: If a date isn't entered in Column B within one week (or 7 days, which ever is easier), the cell in Column A will turn yellow, if no date is entered in Column B within 2 weeks (or 14 days) the cell in Column A will turn Red. If a date is entered in Column B the cell in Column B with the date can turn green.

The ideal formatting would be to add icon sets with with the stop lights instead of highlighting the cell with a color. I believe there is an option to enter a custom formula to apply it with the icon sets, correct? If so, I will do that using the formula above. If not, it's not a deal-breaker.

One other thing, how do I reference a column from another tab so it will add and show two weeks after the date in a column of the other tab. Example - Column A "Quote for Approval" in Tab 1 has a date entered for when the quote was submitted. We need Column B in Tab 2 to add 2 weeks (14 days) and put that in Column A from Tab 1. The conditional formatting for that date in Tab 2 would be based on the original date in Tab 1 but would be the same concept of >7 days, >14 days.

I really hope this makes sense. Please let me know if you have questions and thank you very much for having the forum!!!
 

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,)
I'm confused on your calculations from one sheet to another. But you would use the same concept but the include the sheet name in the cell address reference.
As far as stop lights based on a calculation, I could not see how to do that with out the helper cell I added. And it looks like the icon set did not get copied over with the miniworkbook snippet. But, selet the cell go to Home >>Conditional Formating> Icon Sets > Choose the stop light set set the parameters for each color (I had to reverse order before I set the numbers. Be sure to select number and not percent (which was the default).

Book1
ABCD
1Start DateCheck DateDays Late
21/5/20232/25/202351
3
FirstSheet
Cell Formulas
RangeFormula
C2C2=B2-A2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2Other TypeIcon setNO
A2Expression=(B2-A2)>14textNO
A2Expression=(B2-A2)>7textNO
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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