I need help figuring out date calculations. I've tried many different things and nothing works so far.
I have a list of projects, each project has a list of tasks with start and due dates. I need to calculate the difference between the start date of one task and end date of another task (always the same 2 tasks). In my screenshot, for each project, I want the difference (in days) between Start of Design and End of Plan. I have the desired results displayed on the right (just did a manual calculation, but that won't work because this database is huge!).
Problem is, there are thousands of rows with same project names and same task names. No project has the same task twice , which is very helpful. So I need to have a formula that will let me calculate between dates with conditions:
- Condition 1: Task Name must match "Design" to get the first date
- Condition 2: Task Name must match "Plan" to get the second date
- Condition 3: Both of the above must have Project Name that matches project name @ row
- Then need to subtract one date that meets the above conditions from the other date that meets the above conditions.
I don't know how to do this, I have tried many different formulas and none of them work because once you start playing with index/match/join/collect, dates get converted into text and no calculations can be made.
Any input is appreciated. I would like to do this with one formula because I already have so many different columns that I really don't want to add any additional ones!
PS - these are true date columns and dates are input manually so calculations between start date and due date column shouldn't pose an issue, there is no formula in those columns.
TIA!
