I am trying to create a resource/capacity planner that allows me to play around with when team members are assigned to the project.

I want to be able to add in a number on the days they are assigned to the project (for example 0.5 for half a day, 1 for a full day).

I need the sheet to calculate the start date of a task based on the first cell that has a numerical value.

I don't know how to make the formula reference the corresponding date in the same column.

The current formula I am using is:

=IF(ISNUMBER(INDEX(E9:FE9,MATCH(TRUE,INDEX((E9:FE9<>0),0),0))),E$8, "Not Planned")

Right now, the first part of my formula manages to react to the first number in the row.

I need it to then recognize which column the first number belongs to and reference the corresponding date on row 8.

A copy of the sheet can be found here: EXAMPLE SHEET

Any help is much appreciated!


Reference Image

  • can you share a copy/dummy of your sheet? – user0 Feb 20 '19 at 12:40
  • Hi Romi, it looks like the remainder of your title got cut off. Could you make an edit, please? – jonsca Feb 21 '19 at 3:05
  • Sure! I've made a copy here: docs.google.com/spreadsheets/d/… – Romi Jade Feb 21 '19 at 8:30

I didn't manage to get your formula working in that form. I also don't like the concept of INDIRECT, from a data-integrity standpoint. Let us know if either is a requirement.

I'd suggest FILTERing each given assignments row to find the numbers, MATCHing against that, and returning: the B2 start date plus however many days it had to skip to the right to find the task start. Finally, write Not Planned if there's no match, and write nothing if that row doesn't actually have a Task Name yet.

Set the Start Date cells to your desired "dd mmm yy" or other date formatting, then paste in and 'drag down' the following:
=IF(LEN(A9),IFERROR($B$2-1+MATCH(FILTER(E9:9,ISNUMBER(E9:9)),E9:9,0),"Not Planned"),"")
(All the column E references reflect the new Developer column B in the linked example file; fix to suit your actual file.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.