Here is the breakdown. I have a workbook.
On Sheet B, I have three columns (for sake of clarity, let's call them A, B, and C). These columns have Dates in them. These dates range from January 2019 through December 2020.
Column A is Overall Project Due Date.
Column B is Sustainability Period End
Column C is Validation Due Date
On Sheet A I have one column per month, each row is a specific project number. So, it would look like this:
|Project Number||Jan 19||Feb 19||Mar 19||etc.|
Is it possible to have the following type of IF/Then/Else statement?
If 'SheetB!A2' is between Jan 1, 2019 and Jan 31, 2019, then return A, else leave blank.
OR If 'SheetB!B2' is between Jan 1, 2019 and Jan 31, 2019, then return B, else leave blank.
OR If 'SheetB!C2' is between Jan 1, 2019 and Jan 31, 2019, then return C, else leave blank
ETA: What I have tried is this: IF(AND('Sheet B'!A2)<=DATE(2019,1,31),('Sheet B'!A2)>=DATE(2019,1,1))),"Y","N") as a test. It returns everything FALSE. If I use "OR" instead of "AND" it returns everything TRUE. Even if it's not. I was just testing to see if I could get it to work on just one column at least.
So, I pretty much need the formula to check three columns and tell me if the date is within that month. If it is, which column had a date in that month. If there is no date within that month, ignore it and leave it blank.
I have been trying for the last few working hours to get a formula to work, trying several different permutations, and nothing seems to want to work. Now if I need to consolidate the data to a hidden sheet to make things easier, I can/will. The only caveat is that the spreadsheet's data is fairly dynamic. Some of the dates might end up being updated. New project numbers are added, etc.
What they are looking for is a way to make an "at a glance" grid, with each column on Sheet B being assigned a specific color. That way, if they look at the grid, see a green square for project #45645 in Sept 2019, they know that the Validation Due Date for that project is in September. If it's Purple, they know it's the overall due date. That type of thing.
I really don't want to do this all manually, but will if necessary. I'm just looking for a way to automate this as much as possible. I am prepared for long, unwieldy formulas, which I think this mind end up being.
Any hints, tips, tricks, ideas would be greatly appreciated.