Conditional Formatting to highlight cells between to date

Stef9910 · May 7, 2023

Hello everyone,

I am constructing a gantt chart from cells in pivot table, I am having trouble with the conditional formatting to highlight cells between two dates,

The cells that say blank, were inputted by excel when creating the pivot table, they do not have dates in them.

I am using a pivot table so i can use slicers to filter what is required, as in metro/regional, locations and so on, i have not included that information with the below minisheet.

Any help will be greatly received,

Thank you

Stefan

For mini sheet.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

1

Start Month

April

2

End Month

April

3

4

Status

Type

Region

Hospital

Date Received

Start Date

End Date

RTP

PERSON

5

Cancelled

Scheduled

Metro

Home

(blank)

SMITH

6

Home

SMITH

7

Home

(blank)

SMITH

8

Completed

Scheduled

Metro

Home

(blank)

3/04/2023

4/04/2023

(blank)

SMITH

9

Home

5/04/2023

6/04/2023

(blank)

SMITH

10

Home

(blank)

4/04/2023

(blank)

SMITH

11

Declined

Scheduled

Metro

Home

(blank)

SMITH

12

Home

(blank)

SMITH

13

Full Sit

Scheduled

Metro

Home

(blank)

11/04/2023

(blank)

SMITH

14

Home

SMITH

15

Home

28/04/2023

(blank)

SMITH

16

Home

(blank)

21/04/2023

(blank)

SMITH

17

Home

(blank)

14/04/2023

(blank)

SMITH

18

Not Completed

Unscheduled

Metro

Home

11/04/2023

(blank)

13/04/2023

SMITH

Sheet1

Cell Formulas
Range	Formula
J2,Q2,X2,AE2,AL2	J2	=J3
J3	J3	=I1
K3:AR3	K3	=J3+1
J4:AR4	J4	=LEFT(TEXT(J3,"ddd"),1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J5:AR18	Expression	=IF(AND($G$22<=$K$17,$H$22>$L$17),1,"")	text	NO

PeteWright · May 7, 2023

Stef9910 said:
That's exactly what I am after thank you

Glad I could help

Stef9910 · May 7, 2023

PeteWright said:
Sorry, I think I was wrong.

Maybe this is what you need:

Book1.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR
1 Start Month 45017
2 End Month 45046 01-Apr-23 08-Apr-23 15-Apr-23 22-Apr-23 29-Apr-23
3 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5
4 Status Type Region Hospital Date Received Start Date End Date RTP PERSON S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F
5 Cancelled Scheduled Metro Home (blank) (blank) (blank) (blank) SMITH
6 Home SMITH
7 Home (blank) (blank) (blank) (blank) SMITH
8 Completed Scheduled Metro Home (blank) 4/3/2023 4/4/2023 (blank) SMITH 1 1
9 Home 4/5/2023 4/6/2023 (blank) SMITH 1 1
10 Home (blank) 4/4/2023 4/4/2023 (blank) SMITH 1
11 Declined Scheduled Metro Home (blank) (blank) (blank) (blank) SMITH
12 Home (blank) (blank) (blank) (blank) SMITH
13 Full Sit Scheduled Metro Home (blank) 4/11/2023 4/11/2023 (blank) SMITH 1
14 Home SMITH
15 Home 4/28/2023 4/28/2023 (blank) SMITH 1
16 Home (blank) 4/21/2023 4/21/2023 (blank) SMITH 1
17 Home (blank) 4/14/2023 4/14/2023 (blank) SMITH 1
18 Not Completed Unscheduled Metro Home 4/11/2023 (blank) (blank) 4/13/2023 SMITH
Sheet1
Cell Formulas
Range Formula
J2,Q2,X2,AE2,AL2 J2 =J3
J3 J3 =I1
K3:AR3 K3 =J3+1
J4:AR4 J4 =LEFT(TEXT(J3,"ddd"),1)
J5:AR18 J5 = IF( AND( J$3>=$F5,J$3<=$G5), 1, "")
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
J5:AR18 Cell Value =1 text NO

Would the formula you done work as a new rule in the conditional formatting, instead of applying the formula to each cell?