I have calibration dates that are 1 or 2 years from current, I need to highlight them to display items coming up for calibration 15 days prior to the due date and items that are past the due date. The dates (that need formatting) are displayed in column K and are calculated from the date from column J using =J5+365. I've tried several formulas from various posts with no luck. My Excel knowledge is limited, so I would greatly appreciate any help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
conditional format dates calculated using a formula
- Thread starter rd_CEM
- Start date
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 475
- Office Version
- 365
- Platform
- Windows
- Mobile
Since you didn't mention any formatting and further conditions I made a small table to show you some examples:
Please let me know further details like formatting, other conditions, etc.
| MrExcel_202409.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | ID | mt freq | last | due D | delta | ||
| 2 | 001 | 1 | 11/24/2022 | 11/24/2023 | -293 | ||
| 3 | 002 | 1 | 11/8/2022 | 11/8/2023 | -309 | ||
| 4 | 003 | 1 | 12/25/2023 | 12/24/2024 | 103 | ||
| 5 | 004 | 1 | 11/6/2023 | 11/5/2024 | 54 | ||
| 6 | 005 | 1 | 11/3/2023 | 11/2/2024 | 51 | ||
| 7 | 006 | 1 | 11/20/2023 | 11/19/2024 | 68 | ||
| 8 | 007 | 1 | 9/28/2023 | 9/27/2024 | 15 | ||
| 9 | 008 | 1 | 9/20/2023 | 9/19/2024 | 7 | ||
| 10 | 009 | 1 | 9/13/2023 | 9/12/2024 | 0 | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D10 | D2 | = C2+365 |
| E2:E10 | E2 | = D2-TODAY() |
| C8 | C8 | = TODAY()+15-365 |
| C9 | C9 | = TODAY()+7-365 |
| C10 | C10 | = TODAY()-365 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A2:E10 | Expression | = TODAY()+15<$D2 | text | NO |
| A2:E10 | Expression | = TODAY()<=$D2 | text | NO |
| A2:E10 | Expression | =TODAY()>D$2 | text | NO |
Please let me know further details like formatting, other conditions, etc.
Upvote
0
So what would I use for the formula to conditionally format the dates in column D 15 days prior to the date shown?
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 475
- Office Version
- 365
- Platform
- Windows
- Mobile
This should do it:
Excel Formula:
= $K5 - 15 = TODAY()| MrExcel_202409.xlsm | ||||
|---|---|---|---|---|
| J | K | |||
| 4 | Last Performed | DATE DUE | ||
| 5 | 11/24/2022 | 11/24/2023 | ||
| 6 | 11/8/2022 | 11/8/2023 | ||
| 7 | 12/25/2023 | 12/24/2024 | ||
| 8 | 11/6/2023 | 11/5/2024 | ||
| 9 | 11/3/2023 | 11/2/2024 | ||
| 10 | 11/20/2023 | 11/19/2024 | ||
| 11 | 9/28/2023 | 9/27/2024 | ||
| 12 | 9/29/2023 | 9/28/2024 | ||
| 13 | 9/30/2023 | 9/29/2024 | ||
| 14 | 9/14/2023 | 9/13/2024 | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K5:K14 | K5 | =J5+365 |
| J11 | J11 | = TODAY()+14-365 |
| J12 | J12 | = TODAY()+15-365 |
| J13 | J13 | = TODAY()+16-365 |
| J14 | J14 | = TODAY()-365 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| K5:K14 | Expression | = $K5 - 15 = TODAY() | text | NO |
Upvote
0
Do I need to enter all of the formulas into the rule manager for this to work?. When I enter = $K5 - 15 = TODAY() nothing changes. I also have a formula to highlight the overdue items using <TODAY(). I am unable to post the mini sheet.
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 475
- Office Version
- 365
- Platform
- Windows
- Mobile
In conditional formatting you should enter all the formulas necessary for the formatting to work.
Probably this is not what you wanted since this formula will only highlight cells where the date is exactly 15 days prior to the due date, no day(s) before, no day(s) after.
I don't know the logic of your workbook / worksheet. Is it something like a "live" dashboard (here my suggestion will do the job) or rather "static data" (no dynamic changes), like a simple (To-Do-) list.
Here you would need to adjust the formula a bit.
I think setting up a date range not a fixed date like "highlight all entries 15 prior to due date and beyond", but I may be wrong.
This is what I came up with:
| MrExcel_202409.xlsm | ||||
|---|---|---|---|---|
| J | K | |||
| 4 | Last Performed | DATE DUE | ||
| 5 | 11/24/2022 | 11/24/2023 | ||
| 6 | 11/8/2022 | 11/8/2023 | ||
| 7 | 12/25/2023 | 12/24/2024 | ||
| 8 | 11/6/2023 | 11/5/2024 | ||
| 9 | 11/3/2023 | 11/2/2024 | ||
| 10 | 11/20/2023 | 11/19/2024 | ||
| 11 | 9/28/2023 | 9/27/2024 | ||
| 12 | 9/29/2023 | 9/28/2024 | ||
| 13 | 9/30/2023 | 9/29/2024 | ||
| 14 | 9/14/2023 | 9/13/2024 | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K5:K14 | K5 | =J5+365 |
| J11 | J11 | = TODAY()+14-365 |
| J12 | J12 | = TODAY()+15-365 |
| J13 | J13 | = TODAY()+16-365 |
| J14 | J14 | = TODAY()-365 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| K5:K14 | Expression | = $K5 - 15 < TODAY() | text | NO |
| K5:K14 | Expression | = $K5 - 15 = TODAY() | text | NO |
Let me know if that works or if anything should be changed otherwise.
Upvote
0
It seems to have worked, but it also changed the overdue items to the same format. How can I keep them seperate? The 15-day range will be orange and everything else older will be red.
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 475
- Office Version
- 365
- Platform
- Windows
- Mobile
Hmm, right now it's just guessing. I don't know your conditional formatting setup.
Would you mind sharing a screenshot of your Conditional Formatting?
Upvote
0
Upvote
0
PeteWright
Active Member
- Joined
- Dec 20, 2020
- Messages
- 475
- Office Version
- 365
- Platform
- Windows
- Mobile
How did you actually manage to create this rule? I tried everything and couldn't get the same result.
What I can offer now is this:
| MrExcel_202409.xlsm | ||||
|---|---|---|---|---|
| J | K | |||
| 4 | Last Performed | DATE DUE | ||
| 5 | 11/24/2022 | 11/24/2023 | ||
| 6 | 11/8/2022 | 11/8/2023 | ||
| 7 | 12/25/2023 | 12/24/2024 | ||
| 8 | 11/6/2023 | 11/5/2024 | ||
| 9 | 11/3/2023 | 11/2/2024 | ||
| 10 | 11/20/2023 | 11/19/2024 | ||
| 11 | 9/28/2023 | 9/27/2024 | ||
| 12 | 9/29/2023 | 9/28/2024 | ||
| 13 | 9/30/2023 | 9/29/2024 | ||
| 14 | 9/14/2023 | 9/13/2024 | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K5:K14 | K5 | =J5+365 |
| J11 | J11 | = TODAY()+14-365 |
| J12 | J12 | = TODAY()+15-365 |
| J13 | J13 | = TODAY()+16-365 |
| J14 | J14 | = TODAY()-365 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| K5:K51 | Expression | = $K5 - 15 < TODAY() | text | NO |
| K5:K51 | Expression | = $K5 - 15 >= TODAY() | text | NO |
Can you try it and tell me if it works?
_____________________________________
>>>>>>>>>>EDIT: 9/13/24 19:12
Last edited:
Upvote
0
