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
It now displays the 2 formats but the 15 day one does not seem to be correct, some of the dates are not due until the end of the year or next year
Upvote
0
Excel Facts
Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
PeteWright
Well-known Member
- Joined
- Dec 20, 2020
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
- Mobile
I made another table with the data you have shown and it all seems right:
Could you please point out the wrong results?
| MrExcel_202409.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| I | J | K | L | M | |||
| 4 | Maint. Freq. | Last Performed | DATE DUE | delta | 15d ? | ||
| 5 | 1 year | 11/24/2022 | 11/24/2023 | 295 | TRUE | ||
| 6 | 1 year | 11/8/2022 | 11/8/2023 | 311 | TRUE | ||
| 7 | 1 year | 12/25/2023 | 12/24/2024 | -101 | FALSE | ||
| 8 | 1 year | 11/6/2023 | 11/5/2024 | -52 | FALSE | ||
| 9 | 1 year | 11/3/2023 | 11/2/2024 | -49 | FALSE | ||
| 10 | 1 year | 11/20/2023 | 11/19/2024 | -66 | FALSE | ||
| 11 | 1 year | 9/29/2023 | 9/28/2024 | -14 | TRUE | ||
| 12 | 1 year | 9/30/2023 | 9/29/2024 | -15 | FALSE | ||
| 13 | 1 year | 10/1/2023 | 9/30/2024 | -16 | FALSE | ||
| 14 | 1 year | 9/15/2023 | 9/14/2024 | 0 | TRUE | ||
| 15 | |||||||
| 16 | |||||||
| 17 | |||||||
| 18 | |||||||
| 19 | |||||||
| 20 | Maint. Freq. | Last Performed | DATE DUE | delta | 15d ? | ||
| 21 | 1 year | 1/13/2023 | 1/13/2024 | 245 | TRUE | ||
| 22 | 1 year | 12/22/2023 | 12/21/2024 | -98 | FALSE | ||
| 23 | 1 year | 11/15/2023 | 11/14/2024 | -61 | FALSE | ||
| 24 | 1 year | 12/25/2023 | 12/24/2024 | -101 | FALSE | ||
| 25 | 1 year | 1/15/2024 | 1/14/2025 | -122 | FALSE | ||
| 26 | 1 year | 1/25/2024 | 1/24/2025 | -132 | FALSE | ||
| 27 | 1 year | 1/25/2024 | 1/24/2025 | -132 | FALSE | ||
| 28 | 1 year | 10/31/2023 | 10/30/2024 | -46 | FALSE | ||
| 29 | 1 year | 10/10/2022 | 10/10/2023 | 340 | TRUE | ||
| 30 | 1 year | 10/11/2022 | 10/11/2023 | 339 | TRUE | ||
| 31 | 1 year | 11/8/2022 | 11/8/2023 | 311 | TRUE | ||
| 32 | 1 year | 2/3/2023 | 2/3/2024 | 224 | TRUE | ||
| 33 | 2 years | 11/13/2023 | 11/12/2025 | -424 | FALSE | ||
| 34 | 1 year | 9/10/2024 | 9/10/2025 | -361 | FALSE | ||
| 35 | 1 year | 9/12/2023 | 9/11/2024 | 3 | TRUE | ||
| 36 | 1 year | 11/4/2022 | 11/4/2023 | 315 | TRUE | ||
| 37 | 1 year | 5/3/2023 | 5/2/2024 | 135 | TRUE | ||
| 38 | 2 years | 9/10/2024 | 9/10/2026 | -726 | FALSE | ||
| 39 | |||||||
| 40 | 1 year | 9/10/2024 | 9/10/2025 | -361 | FALSE | ||
| 41 | 1 year | 1/12/2024 | 1/11/2025 | -119 | FALSE | ||
Sheet1 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K40:K41,K21:K38,K5:K14 | K5 | = J5 + 365 * I5 |
| L40:L41,L21:L38,L5:L14 | L5 | = TODAY() - K5 |
| M40:M41,M21:M38,M5:M14 | M5 | = L5 > -15 |
| 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 | |
| M5:M50 | Cell Value | contains "FALSE" | text | NO |
| M5:M50 | Cell Value | contains "TRUE" | text | NO |
| K5:K50 | Expression | = $K5 - 15 < TODAY() | text | NO |
| K5:K50 | Expression | = $K5 - 15 >= TODAY() | text | NO |
Could you please point out the wrong results?
Upvote
0
The items highlighted in orange should remain black (or unformatted) until they are within 15 days of the Due Date.
Upvote
0
Upvote
0
When I use = TODAY()+15-365 it highlights everything in orange. Am I doing something wrong on my end? I feel like I'm missing something. Which formula should I be using?
Upvote
0
PeteWright
Well-known Member
- Joined
- Dec 20, 2020
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
- Mobile
Oh, you shouldn't use that formula at all! I made it just for testing the conditional formatting. Sorry for the confusion.
| J11 | J11 | = TODAY()+14-365 |
| J12 | J12 | = TODAY()+15-365 |
| J13 | J13 | = TODAY()+16-365 |
| J14 | J14 | = TODAY()-365 |
Those formulas simply generate dates for testing the 15 day range.
Please remove those formulas and let me know if things work after that.
Upvote
0
If I remove that formula all I have left is what I'm using to format the dates that are older than todays date. How would the 15 day conditional format work? I'm lost...LOL
Attachments
Upvote
0
PeteWright
Well-known Member
- Joined
- Dec 20, 2020
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
- Mobile
Try to set up your conditional formatting like this:
Upvote
0
Solution
that worked, not sure what my issue was previously. Thanks for the help.
Upvote
0
