conditional format dates calculated using a formula

rd_CEM · Sep 12, 2024

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.

PeteWright · Sep 12, 2024

Since you didn't mention any formatting and further conditions I made a small table to show you some examples:

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.

rd_CEM · Sep 13, 2024

PeteWright said:
Since you didn't mention any formatting and further conditions I made a small table to show you some examples:

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.

So what would I use for the formula to conditionally format the dates in column D 15 days prior to the date shown?

PeteWright · Sep 13, 2024

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

12

13

14

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

rd_CEM · Sep 13, 2024

PeteWright said:
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

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.

PeteWright · Sep 13, 2024

rd_CEM said:
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.

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

12

13

14

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.

rd_CEM · Sep 13, 2024

PeteWright said:
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

View attachment 116821

Let me know if that works or if anything should be changed otherwise.

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.

PeteWright · Sep 13, 2024

rd_CEM said:
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.

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?

rd_CEM · Sep 13, 2024

PeteWright said:
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?