Help with building a Formula "OR" vs "AND"

[AudyAnalyst]

Hello,

I am trying to make an "OR" formula (I think). I have two things I need to see in my chart. If the month on the Top Row (E1:P1) falls between the 'Open Date' (C:C) and 'Closed Date'(D:D). However, I need the formula to prioritize the 'Status' (A:A) "Closed". If the Status is "Closed", then there needs to be a "c" in the corresponding month that is equal to the 'Closed Date'(D:D). But I still would like the formula to make an 'o' or 'IP' from when it was first opened (What I am looking for):

My Current Formula is: =IF(AND(K$1>=$C7,K$1<=$D7,$A7="Open"),"o",IF(AND(K$1>=$C7,K$1<=$D7,$A7="In Progress"),"IP",IF(AND(K$1>=$C7,K$1<=$D7,$A7="Closed"),"c"," ")))
And what that looks like currently:

Think it's possible ? Any help or ideas would be great!

 

[Fluff]

Can you post a sample of your data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jasonb75]

Does row 1 contain dates such as 1/1/2021 formatted as mmm? If so, are they all 1st day of the month?

Noting that row 7 has a Feb opening date, should anything be shown in F7 to reflect that?

 

[AudyAnalyst]

Does row 1 contain dates such as 1/1/2021 formatted as mmm? If so, are they all 1st day of the month?

Noting that row 7 has a Feb opening date, should anything be shown in F7 to reflect that?

Yes, It should. The F2 is 02/01/2021.
I don't know why it wouldn't.

 

[AudyAnalyst]

Can you post a sample of your data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I'm sorry, I'm on a work computer and can not download anything. Hope this helps:

Status

Project Number

Open date

Closed Date

1/1/2021

2/1/2021

3/1/2021

4/1/2021

5/1/2021

6/1/2021

7/1/2021

8/1/2021

9/1/2021

10/1/2021

11/1/2021

12/1/2021

Open

P001

1/1/2021

12/31/2999

o

o

o

o

o

o

o

o

o

o

o

o

In Progress

P002

1/1/2021

12/31/2999

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

In Progress

P003

1/1/2021

12/31/2999

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

IP

Open

P004

12/1/2021

12/31/2999

o

Closed

P005

11/1/2021

12/31/2999

c

c

Closed

P006

2/8/2021

7/6/2021

c

c

c

c

c

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Status	Project Number	Open date	Closed Date	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021
2	Open	P001	01/01/2021	31/12/2999	o	o	o	o	o	o	o	o	o	o	o	o
3	In Progress	P002	01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
4	In Progress	P003	01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
5	Open	P004	01/12/2021	31/12/2999												o
6	Closed	P005	01/11/2021	31/12/2021											o	c
7	Closed	P006	08/02/2021	06/07/2021		o	o	o	o	o	c
Main

Cell Formulas
Range	Formula
E2:P7	E2	=IF(AND($A2="Closed",TEXT($D2,"mmyyyy")=TEXT(E$1,"mmyyyy")),"c",IF(AND(EOMONTH($C2,-1)<=E$1,$D2>=E$1),SWITCH($A2,"In Progress","IP","o"),""))

 

[AudyAnalyst]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Status	Project Number	Open date	Closed Date	01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021
2	Open	P001	01/01/2021	31/12/2999	o	o	o	o	o	o	o	o	o	o	o	o
3	In Progress	P002	01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
4	In Progress	P003	01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
5	Open	P004	01/12/2021	31/12/2999												o
6	Closed	P005	01/11/2021	31/12/2021											o	c
7	Closed	P006	08/02/2021	06/07/2021		o	o	o	o	o	c
Main

Cell Formulas
Range	Formula
E2:P7	E2	=IF(AND($A2="Closed",TEXT($D2,"mmyyyy")=TEXT(E$1,"mmyyyy")),"c",IF(AND(EOMONTH($C2,-1)<=E$1,$D2>=E$1),SWITCH($A2,"In Progress","IP","o"),""))

Perfect! That solved it. Thank you!

 

[jasonb75]

I was a bit slow getting it working but I'll post mine anyway. Check P7 in @fluffs suggestion, should that be "c" or "o"?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021
2	Open		01/01/2021	31/12/2999	o	o	o	o	o	o	o	o	o	o	o	o
3	In Progress		01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
4	In Progress		01/01/2021	31/12/2999	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP	IP
5	Open		01/12/2021	31/12/2999												o
6	Closed		01/11/2021	31/12/2999											o	o
7	Closed		08/02/2021	06/07/2021		o	o	o	o	o	c
Sheet1

Cell Formulas
Range	Formula
E2:P7	E2	=IF(OR(EOMONTH($C2,-1)+1>E$1,EOMONTH($D2,-1)+1<E$1),"",IF($A2="In Progress","IP",IF(AND($A2="Closed",EOMONTH($D2,-1)+1=E$1),"c","o")))

 

[Fluff]

Perfect! That solved it. Thank you!

Glad we could help & thanks for the feedback.

Check P7 in @fluffs suggestion, should that be "c" or "o"?

I cheated & change the closing date for that row.