How do i get these conditional formats correct?

[V20agent]

I'm trying to conditional format the gantt chart based on start and end dates, but some reason its not working correctly.
if you see line 7, it starts in Jan but the section is not colored in Jan.

What am i doing wrong here?

https://gofile.io/?c=kWq3g9

Appreciate the help.

Thanks

 

[jasonb75]

Very few members (if any) will follow a download link provided by someone who has only just joined the forum.

You can use xl2bb to post your data sample directly to the forum.

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[Fluff]

Can you post some sample data using the XL2Bb add-in?

 

[V20agent]

ah ok im sorry i didnt know how to post it.

basically have this formula in the conditional formatting to color the cells based on the date ranges
=AND(E$5>=$B6,E$5<=$C6)


here it goes

simple-gantt-chart_ms.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
1
2
3	Display Month	1/31/2020
4					2020												2021
5	Task	Start	End		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	block block	1-Jan-20	6-Dec-20
7	blb	15-Jan-20	25-Feb-20
8	bolb 1	1-Jan-20	30-Jun-20
9	block 3	15-Jan-20	22-Jun-20
10	block 5	15-Mar-20	23-May-20
11	block 6	1-Mar-20	30-Dec-20
12	block 39	1/2/2020	6/22/2020
13
14	block
15	block 18	2/25/2020	9/30/2021
16
17
18
19
Sheet1

 

[Fluff]

Thanks for that, how about
=AND(E$5>=EOMONTH($B6,-1)+1,E$5<=$C6)

 

[V20agent]

Thanks for that, how about
=AND(E$5>=EOMONTH($B6,-1)+1,E$5<=$C6)

this works perfectly! MANY thanks and apologies about the link. i could not edit to delete

 

[Fluff]

Glad we could help & thanks for the feedback