If Statement Comparing Date of Text Entry Between Columns

lmckaig96366

We work from a running tracker were there are a series of dates (entered as text, not an actual date) are entered into different columns to signify when certain steps have been completed. I am trying to find a way to automate a flag or alert or something to designate how many days go between each step so we can easily see where the process is getting hung up. I know I can go in and view cell history to see when things are entered but I need a way to automate that. I'm currently thinking of two options, preference being option 2 if it's possible. 

1) create some sort of IF statement comparing date of entry between two columns that outputs a flag or alert if more than a set time goes by from entry in the first column before something is entered into the second column.

      1.1) a formula that flags or alerts automatically three days after entry into the first column. I could make this work though it is less desirable. 

2) create a formula to read when the initial entry is entered into the first column compared against when something is entered into the second column. Here, we are more interested in how long it is taking from something being entered and that line being addressed (something entered into the second column) If there is a formula to call out how many days have passed I think it would solve my need to identify bottlenecks perfectly.

Andrée Starå

Hi Lindsey,

I can think of a few ways to structure this. The right solution depends on the specifics.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

lmckaig96366

Hi Andrée, 

it's the running contract tracker I've shared with you. We're trying to call out dates between several different columns, I've found how to do it between the columns that are actually dates (I'll just have to change the columns from text/number columns to actually date columns so I can use the networkday function) Where I'm running into trouble is extracting a date of entry from the columns that are either check boxes or drop down menus. It sounds like this is a common problem on SmartSheet that they have not addressed yet. I need a date for when something is initially entered into a specific cell so the date modified does not work as it pulls a date for any entry/change to the whole row. Specifically on the sheet I shared with you, we need to compare the date of when the "Pricing Received" column reads "true" (far right on the sheet, red in row 1. this is an if statement set up to read true when either or both of two check boxes are checked.) with the "Ready for CM Review" date (kind of in the middle, blue in row one) we then need to compare that date to the date of when any option is chosen in the drop down menu on the "Approved by CM" column.(immediately to the right of the "Ready for CM Review" column.