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.
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.
