Calculations with dates

Melisa Dannhauser

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!

David Joyeuse

Hi @Melisa Dannhauser

How about this:

=DATE(COLLECT({Due Date Range}, {Project Name Range}, [Project]@row, {Task Name Range}, "Plan") - DATE(COLLECT({Start Date Range}, {Project Name Range}, [Project]@row, "Design")

Let me know how this works.

Hope it helped!

Melisa Dannhauser

@David Joyeuse That doesn't work unfortunately. = Date(collect) is expecting a date entry so when I finish entering the first part of the formula, it thinks that's the year and it wants me to now enter month and day... See screenshot. Any other suggestions? I tried using MIN(collect) but I have the same issue as always - the returned date isn't really recognized as a date so I can't perform a calculation. I have no idea if what I'm trying to do is possible :(

These are my actual column names below.

Melisa Dannhauser

Ahh, after playing around with it, I realized that MIN(COLLECT) does work! I just had the column formatted as a date so it kept saying "date expected". After formatting column as text/number, the calculation did work! Thanks a lot for pointing me in the right direction with collect! :)

My working formula looks exactly like David's above except I replaced DATE with MIN :)