Welcome!

Function

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!

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Why am I not getting reminders?
My reminder in smartsheet automation is set to trigger every day starting 12/05/2025 at 8am (gmt+1) and the condition is to send the reminder to the contact in the contact list if the reminder flag is set to 1 and the status is not one of DONE. Currently on the sheet, a task has the reminder flag set to 1 and the status is…
I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Membership/ Access question
Hi Everyone, My IT has a lot of confusion on what we access we really need to have for certain people. What is the difference between a "Member" and a "Licensed User?" We have a lot of sheets that have owners who do all of the changes of the sheet. I know that they need to be licensed users. But then, we have a lot of…