Unable to determine logic - help

[JohnMense]

Hoping someone can help on this logic issue I am having. Usually I am able to come up with the right equation with nested IFs but for some reason this has me a bit stumped.

I am trying to make a large chart showing for a given month how many doors are considered active. Active starts once a door is first used and then ends when there is a deletion date.
The raw data (worksheet 'Data') contains a list of Unique Numbers (doors in this case) with information on the date it was first used (can be blank as it is only vald once used), and the date it was deleted (if deleted so could be also blank). I created 2 columns (A & B) where I extract the date (Year & month only) so it can be turned into a numerical value. I do this as using the extraction of just YYYY-MM was giving me issues when trying to determine if a reference date is higher or lower (3rd worksheet 'Tables')

I created pivots (2nd worksheet) so I can see all unique doors, what doors have a first use date, and what doors are deleted.
The 3rd worksheet (Tables) is where I am trying to see for a given month how many doors are active. Top row is the YYYYMM and I compare that value against the door ID and put a '1' on the month it matches first use (or keep blank).

The issue is trying to then stop the '1' from showing when a deletion date is seen, or just keep putting it.

Overall logic is to be:
- First-use: Start putting '1' when a door has a First Use date equal or greater than the reference YYYYMM, otherwise keep blank
- Deletion Date: if a door has a deletion date stop putting a '1' (put blank) as of the following month (ie: reference month in row A is > deletion date)

Anyone have an idea how to best achieve this?

I don't see where I can upload the file so have shown an image of the tables and the first part of the formula that I have figured out.

Much appreciated.

 

[offthelip]

try this:

=IF($B2="","",IF(AND(E$1>=$B2,$C2>=E$1),"1",0))

 

[JohnMense]

Unfortunately that gives 0 in every cell.

 

[Fluff]

How about
=IF($B2="","",IF(AND(E$1>=$B2,E$1<=MIN($C2,999999)),1,""))

 

[JohnMense]

So I took the first suggestion and got part of the way by adding in VALUE where needed. So I now have the formula:
=IF($B2="","",IF(AND(VALUE(E$1)>=VALUE($B2),VALUE($C2)>=E$1),"1",0))

When there is both a first use date and a deletion date this works well, but when the deletion date is empty it gives an error when it should continue with the '1' in each cell when the month is greater or equal to the first-use date.

 

[JohnMense]

So I took the first suggestion and got part of the way by adding in VALUE where needed. So I now have the formula:
=IF($B2="","",IF(AND(VALUE(E$1)>=VALUE($B2),VALUE($C2)>=E$1),"1",0))

When there is both a first use date and a deletion date this works well, but when the deletion date is empty it gives an error when it should continue with the '1' in each cell when the month is greater or equal to the first-use date.

I will try Fluff's suggest with VALUE and see what happens

 

[JohnMense]

Nope, that didn't work unfortunately

 

[Fluff]

Can you post some sample data using the XL2BB add-in?
Also why not keep your dates as dates? rather than messing about with them.

 

[offthelip]

try this mod to the formula you posted:
=IF($B2="","",IF(AND(VALUE(E$1)>=VALUE($B2),OR(VALUE($C2)>=E$1,$C2="")),"1",0))

 

[JohnMense]

Will try and post sample data as you recommend.

Reason for not keeping dates as dates is because the first-use date and deletion dates are not recognized as dates for some reason. From a long date and time format I extract only the year and month (TEXT,...,"YYYMM"). I tried to format the cells as a date but it still doesn't like it. As example if E2 is a value of "8/14/2008 6:55:00 PM" then I just want TEXT(E2,"yyymm"). I lose the fact it is a date structure, etc. That is the least of my worries....

Trying XL2BB code below. Is that the right thing?

2020-03-09 - R Wengle Account issues.xlsx
	A	B	C	D	E	F	G	H	I
1	Door ID	First Use	Deletion	200807	200808	200809	200810	200811	200812
2	S: 513 D: 10	200808	201111	0	1	1	1	1	1
3	S: 513 D: 11	200808	200811	0	1	1	1	1	0
4	S: 513 D: 12		200811
5	S: 513 D: 13	200810	200811	0	0	0	1	1	0
6	S: 513 D: 16	201112		0	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Tables

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(INDEX(Pivots!B$3:B$623,MATCH(A2,Pivots!A$3:A$623,0)),"")
C2:C6	C2	=IFERROR(INDEX(Pivots!F$3:F$623,MATCH(A2,Pivots!E$3:E$623,0)),"")
D2:D5,E2:I6	D2	=IF($B2="","",IF(AND(VALUE(D$1)>=VALUE($B2),VALUE($C2)>=D$1),1,0))
D6	D6	=IF($B6="","",IF(AND(VALUE(D$1)>=VALUE($B6),IF($C6="","",VALUE($C6)>=D$1)>=D$1),1,0))