Automatically enter certain Text in a cell if Date Open > 60 days

SPRINGBOK

I currently have 2 formulas that work in conjunction to look at Acct Open Date and if it's greater than 60 days it populates a column/cell with yes or no (via checkbox) then, looks to a SECOND column/cell and tells the first cell to enter the text that is in the reference cell. It works fine but it relies on me copying and pasting the Text in the reference cell down a bunch of rows ever so often. SURELY there is a better way to do this now since I set it up a few years ago. I can get the auto populate formula tool to work but my system still relies on me dragging down and copying the reference text...sigh...I attached a screen shot

Paul Newcome

Are any of the cells having the formula overwritten by manual entry?

 

Are you inserting rows into the middle (not in row 1 or creating a new row at the bottom of the sheet?

 

Is there any consistency with which you have to go through this process?

SPRINGBOK

#1 - not sure what you mean by "overwritten by manual entry" sorry

#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom  neither of those options will work with the autofill formulas, correct?

#3 - yes, every account that is opened goes through this process 

Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to  respectively calculate [date open] + 2 days, [date open] + 14 days,  [date open] + 60 days.

I'm the worst at explaining these details, forgive me

 

Andrée Starå

Hi,

#1 - not sure what you mean by "overwritten by manual entry" sorry

Will you change the value manually?

#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom  neither of those options will work with the autofill formulas, correct?

Fortunately, no. If the two rows under or above have the formulas, it will work.

#3 - yes, every account that is opened goes through this process 

Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to  respectively calculate [date open] + 2 days, [date open] + 14 days,  [date open] + 60 days.

I'm the worst at explaining these details, forgive me

No, you're explaining it just fine. Let's see if I got it! 

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

Try something like this.

Add the first one to the cells that you want to display Pending. Add the second one to the date columns and change the +2 to + 14 and +60 as needed.

=IF(ISDATE([Date Open]@row); "Pending")

The same version but with the below changes for your and others convenience.

=IF(ISDATE([Date Open]@row), "Pending")

=IF(ISDATE([Date Open]@row); [Date Open]@row) + 2

The same version but with the below changes for your and others convenience.

=IF(ISDATE([Date Open]@row), [Date Open]@row) + 2

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Paul Newcome

#1 - not sure what you mean by "overwritten by manual entry" sorry

 

What Andree said... 

.

#2 - we are about to change to using a Form to create each new row and I was actually wondering how that would work given that the row created via the Form has to be added to the top or bottom  neither of those options will work with the autofill formulas, correct?

 

Also what Andree said... 

.

#3 - yes, every account that is opened goes through this process 

Let me explain the end goal and maybe there's a MUCH easier way to do it. Essentially, when we add the [Date Open] date we want that to kick off the Checks/Debit Card/Online Banking columns to populate with the text "Pending"...simultaneously, we want the 2 Day/2Week/2 Mo follow up columns to  respectively calculate [date open] + 2 days, [date open] + 14 days,  [date open] + 60 days.

I'm the worst at explaining these details, forgive me

 

And again... What Andree said... Haha. You're explaining just fine. I do have another question though...

.

.

Is there something that regularly happens that is causing you to have to "copying and pasting the Text in the reference cell down a bunch of rows ever so often"?

.

Switching over to forms is actually a great way to ensure consistency when it comes to autofilling just so long as additional manual edits to a row are done with caution. It is very likely that this step alone will alleviate most of your issues.

SPRINGBOK

Brilliant! 1,000 thank yous!

Andree, your simplified formulas worked great. 

Paul, you made me realize that I can use hidden fields in a Form to keep from having to drag/copy data down the sheet! Yay!

My last hurdle is this: Our Master Tracking sheet has the 3 columns with the =IF(ISDATE([Date Open]@row), [Date Open]@row) + 14 formulas. These help the account opening team bc the 3 dates for various milestones auto populates based on "Date Open" so they don't have to do it manually. These 3 columns with the dates map out to a Report I have built for our admins to track the 3 milestones and when they complete each of them (callback, thank you letter, and 2 month follow up) they need to be able to change the date to text (something like, done or complete, or something)...problem is they obvio can't change the date in the Report bc it's a formula on the Master Tracking sheet. Is there a work around for this scenario?

Many thanks again,

Shelley

 

Andrée Starå

Excellent!

Happy to help!

Andrée Starå

A possible workaround could be to add other columns for the manually entered text and update the formulas in the date columns to show the text if it's not empty. Could also work with checkboxes instead.

Would that work?

Nick Burrus

Hey there! I don't have time t odig into this too much. But, for dates I like to use formulas that let you set a criteria

example: {Date}, >TODAY(-60)

Something like:

=IF([Date Open] >TODAY(-60), "True", "False")

David Brandt

I am following some of these examples to create a similar formula/action. I would like to use the Smartsheet Start and End columns, which have dates (month/day/year) in them to spit out a "text" into another column for every row. I'm creating the formula in this other column.

Was thinking along these lines...

=IF(AND([Start]1@row>= DATEONLY(2020, 2, 1), [End]1@row<= DATEONLY(2020, 5, 1)), "FY2021,Q1"

... but it is not working.

What is the best/right way to do this?

Thanks in advance for the help.

Andrée Starå

Hi @David Brandt

I hope you're well and safe!

Try something like this.

=IF(AND(Start@row >= DATE(2020, 2, 1), End@row <= DATE(2020, 5, 1)), "FY2021,Q1")

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

David Brandt

Andree - FANTASTIC! This solves it.... Thank you!

One related question... Can I extend this same logic you shared for several date ranges with respective quarterly "text" (so that depending on the start/end date range for a given row the right quarter text appears) and separate each additional start/end/text statement with a comma like what you have shared? Or does it require changing the structure of the entire expression/statement?

This seems intuitive but may not be logical.

Thanks again.