Fill cells if between dates

[ashley1984]

Hi,

I'm trying to fill cells if they are between a specific date as entered by a user. E.g.

If I had the dates as entered (dd/mm/yyyy) between 5th Jan & December 2021, then all the cells under the corresponding month would fill with my chosen colour.

If I had to change the end date to 01/06/2021 then the cells between and June would be filled

thanks

Start Date	End Date	Jan	Feb	March	April	May	June	Jul	Aug etc
05/01/2021	31/12/2021

 

[Fluff]

Are the month names just text or dates formatted to show the month?
Also what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ashley1984]

Months are just text but can change to date format if needed.

I'm using 365 on Windows. Updated my user profile to reflect this

 

[JamesCanale]

Conditional formatting formula:

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
2	1/5/2021	12/31/2021
3	6/5/2021	6/25/2021
4	2/15/2021	9/30/2021
5	1/5/2021	2/1/2021
6	2/14/2021	9/29/2021
Sheet32

Cell Formulas
Range	Formula
D1:N1	D1	=EOMONTH(C1,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:N6	Expression	=SUM(--(MONTH(C$1)=ROW(INDIRECT(MONTH($A2)&":"&MONTH($B2)))))=1	text	NO

 

[Fluff]

Thanks for that, if you convert them to dates you can use

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	05/01/2021	31/12/2021
3	05/04/2021	07/10/2021
4	01/06/2021	01/08/2021
5
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:N4	Expression	=AND($A2<>"",$B2<>"",MONTH($A2)<=MONTH(C$1),MONTH($B2)>=MONTH(C$1))	text	NO

 

[ashley1984]

Conditional formatting formula:

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
2	1/5/2021	12/31/2021
3	6/5/2021	6/25/2021
4	2/15/2021	9/30/2021
5	1/5/2021	2/1/2021
6	2/14/2021	9/29/2021
Sheet32

Cell Formulas
Range	Formula
D1:N1	D1	=EOMONTH(C1,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:N6	Expression	=SUM(--(MONTH(C$1)=ROW(INDIRECT(MONTH($A2)&":"&MONTH($B2)))))=1	text	NO

Thanks, this works to some extent. Few questions, your dates appear to be American, how do I do this in UK (DD/MM/YYYY). I managed to get it to work but how do I get this to fill into 2022. For example, if the start date is 12th September 2021 but the end date is only the 1st March 2022 how would I do this? I've set up the =EOMMONTH formula to go to the end of Dec 2023, starting Jan 2021 and applied the conditional formating to the whole date range

 

[ashley1984]

Thanks for that, if you convert them to dates you can use

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	05/01/2021	31/12/2021
3	05/04/2021	07/10/2021
4	01/06/2021	01/08/2021
5
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:N4	Expression	=AND($A2<>"",$B2<>"",MONTH($A2)<=MONTH(C$1),MONTH($B2)>=MONTH(C$1))	text	NO

Thanks, this also works to some extent. I should have been clearer in my original post that my date range extended to December 2023, so I might have dates over two years e.g. 01/09/21 to 03/06/22. Your suggestion works to some extent but if I enter 01/01/21 to 09/09/21 the cells highlight the correct months but then the cells for 2022 & 2023 are filled in as well?

 

[JamesCanale]

Thanks, this works to some extent. Few questions, your dates appear to be American, how do I do this in UK (DD/MM/YYYY). I managed to get it to work but how do I get this to fill into 2022. For example, if the start date is 12th September 2021 but the end date is only the 1st March 2022 how would I do this? I've set up the =EOMMONTH formula to go to the end of Dec 2023, starting Jan 2021 and applied the conditional formating to the whole date range

Date format doesn't matter. THe month function will work. But fluff's is a better function. IF you want to get years involved it gets a bit more complicated.

Everywhere you have "Month(XX), replace it with "100*year(xx)+month(xx)".

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
2	05/01/2021	31/12/2021
3	05/04/2021	07/10/2021
4	01/08/2021	07/03/2022
5
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:Q5	Expression	=AND($A2<>"",$B2<>"",EOMONTH($A2,-1)+1<=C$1,EOMONTH($B2,0)>=C$1)	text	NO