Function

Heather Dixon

Wondering if anyone knows if this can be done...

 

I have two date columns and a column that I need populated based off of if a date is populated.

Column 1 - Phase Start Date

Column 2 - Phase End Date

Column 3 - Phase

There are children phases listed, and I need the function to be, if Column 1 is populated and Column 2 is not populated, Column 3 will be populated with the phase name.

It would be amazing if the function could have an "And" so that I can have the "Parent" row populated in the Phase Column instead of each "Children" Column being populated.

 

Thanks in advance!

Mike Wilday

You would want to build a nested if statement starting with the last phase down to the first phase... 

Assuming Discovery is row 2, Admin is row 3, etc.. then use this formula

=IF(ISDATE([Phase End Date]6), "Completed", IF(ISDATE([Phase End Date]5), "Go Live (Complete)", IF(ISDATE([Phase End Date]4), "Live Planning", IF(ISDATE([Phase End Date]3), "Testin", IF(ISDATE([Phase End Date]2), "Admin", IF(ISBLANK([Phase End Date]2),"Discovery"))))))

It's checking each of the end dates. If the end dates are populated with a date, then it presents the status of the one after it. You start with the last one first because that will be the last date to be marked true... 

L_123

So this can be done without having to build conditional if statements/line, though it is a very complicated formula, especially if you aren't comfortable with spreadsheet formulas. If you aren't comfortable with formulas, stop here and use Mr. Mike's idea. The advantage of this way is you can simply use the parent children format to build references, and not have to edit the formula when you add new firms. I didn't do the parent child references and simply used column references instead, but the formula is below

 

=LEFT(JOIN(COLLECT([Firm Name]:[Firm Name], [Phase Start Date]:[Phase Start Date], ISDATE(@cell), [Phase End Date]:[Phase End Date], ISBLANK(@cell))), FIND(".", JOIN(COLLECT([Firm Name]:[Firm Name], [Phase Start Date]:[Phase Start Date], ISDATE(@cell), [Phase End Date]:[Phase End Date], ISBLANK(@cell))), 3) - 2)

 

In order for this formula to work you need to add an identifier to the firm name. Simply naming them 1. Discovery 2. Admin or A. Discovery  B. Admin will work. The formula uses the period to parse the text

L_123

What this formula does:

Collect:

If phase start date has a date and phase end date is blank it grabs the firm name for all that occur

Join

Takes all of the information from collect and puts it into a chain of text

Left/Find

Finds the second period in the chain of text, moves left two digits, and cuts everything to the right of that off

 

It is a convoluted way to do/think about this problem, but it is the only way I've found other than Mr. Mikes way

L_123

=LEFT(JOIN(COLLECT(CHILDREN([Firm Name]2), CHILDREN([Phase Start Date]2), ISDATE(@cell), CHILDREN([Phase End Date]2), ISBLANK(@cell))), FIND(".", JOIN(COLLECT(CHILDREN([Firm Name]2), CHILDREN([Phase Start Date]2), ISDATE(@cell), CHILDREN([Phase End Date]2), ISBLANK(@cell))), 3) - 2)

 

Edited for parent child reference given parent on row 2

L_123

*made a small mistake The find is moved with my formula which means if there is only one entry it gives an error. This was fixed with an if statement

=LEFT(JOIN(COLLECT(CHILDREN([Firm Name]2), CHILDREN([Phase Start Date]2), ISDATE(@cell), CHILDREN([Phase End Date]2), ISBLANK(@cell))), IF(COUNTIFS(CHILDREN([Phase End Date]2), ISBLANK(@cell), CHILDREN([Phase Start Date]2), ISDATE(@cell)) > 1, FIND(".", JOIN(COLLECT(CHILDREN([Firm Name]2), CHILDREN([Phase Start Date]2), ISDATE(@cell), CHILDREN([Phase End Date]2), ISBLANK(@cell))), 3) - 2, 100)) 

 

BTW thanks for this problem. Really got me thinking about different ways this could be applied to my own issues. 

Heather Dixon

Thanks Mike!

Heather Dixon

Hi Luke,

Thank you so much for taking the time to really research this and build it out for me.  I'm not sure if I'm missing something, but when I copy this into my sheet and populate dates the function does not produce any results. 

Thanks again!

Heather

L_123

Yeah I locked it into row references without thinking. That one will only work on row 2. try this one:

 

=LEFT(JOIN(COLLECT(CHILDREN([Firm Name]@row), CHILDREN([Phase Start Date]@row), ISDATE(@cell), CHILDREN([Phase End Date]@row), ISBLANK(@cell))), IF(COUNTIFS(CHILDREN([Phase End Date]@row), ISBLANK(@cell), CHILDREN([Phase Start Date]@row), ISDATE(@cell)) > 1, FIND(".", JOIN(COLLECT(CHILDREN([Firm Name]@row), CHILDREN([Phase Start Date]@row), ISDATE(@cell), CHILDREN([Phase End Date]@row), ISBLANK(@cell))), 3) - 2, 100))

 

You can see it work here:

 

https://app.smartsheet.com/b/publish?EQBCT=50df1fa6df5a4b0ba5467245fc41195d