Helper columns to allow parent row data to be included in automation

saltyblueocean

Hello!

I am trying to work on refining our automation in a project management tracker we have. Our parent row has the start and end date for the entire project. I am trying to create a helper column that would automatically count days to end of project for each project and repeat that number for each line of a project to simplify automation. That way when sending out automation it would say, "such and such process has been completed and we are # days away from the end of this project."

I am hoping to make it an automatic calculation that doesn't need to be regularly managed that way the automation applies every time a new project is added to the sheet.

Below is a snapshot of the sheet. The "End" date associated with the Project Name is the date I would be using to calculate the "Days to STR"

Genevieve P.

Hi @saltyblueocean

This would actually be pretty easy, if I understand what it is you're looking to do. You can use the TODAY function to look at Today's date and compare it to the End date, returning a number between the two:

=End@row - TODAY()

This would return a number, so you'd want the Helper column to be a Text/Number column. Drag-fill this down the entire column and it will update to show you the number of days until the End date for that row. (See here for more information about using dates in formulas).

You could set up Alerts or conditional formatting if this date shows as negative number (meaning the end date is in the past). Or we could embed this in an IF statement that says if the Status column is Complete for that row, return "Complete", but if the status is not complete, return the number of days until the End date, negative or positive. Let me know if you're interested in this and need help creating this formula as well.

You will want to keep in mind that TODAY needs the sheet to be accessed in some way for the formula to update and recognize what the date is. Our Help Center article on TODAY has a list of all the ways a sheet can update: https://help.smartsheet.com/function/today

Cheers!

Genevieve

saltyblueocean

Thanks Genevieve!

I already had a column for TODAY, but I also figured out how to ensure that the same days to STR appeared on each line even if the end date is different by using a parent formula to spit out the end date of the parent column. That solved it.

Cheers,

Genevieve P.

Oh I see!! Yes, that would work - I'm glad you figured it out 🙂