Trouble with RGYB and blank dates

Fletcher Boll

Screen Shot 2020-02-05 at 5.18.47 PM.png

Screen Shot 2020-02-05 at 5.22.33 PM.png

Screen Shot 2020-02-05 at 5.22.14 PM.png

Hello!

I am working with a Health column that refers to our Status column and start/end dates. I have a formula that is working once the dates are entered, but when I use a fresh templated sheet and there are no dates, it looks as if all the tasks are delayed. (see pic)

I would like:

• Green if complete
• Yellow in in progress
• Red if delayed by not started past end date
• blue if not started
• and preferably no symbol if the status is N/A or has no dates

Also, I am having trouble with cells auto-filled. In pic #2, I entered the date in row 14 and it auto-filled all the ones below. I don't want that. And they aren't blue for not started with a future date!

Here is the current formula that IS working (if dates are entered)

=If(Status)14="complete", "Green", IF(AND(Status14 <> "complete", TODAY()>[end date]14, "Red", IF(AND(Status14= "Not Started", "Blue", "Yellow")))

Please help! I have a major deadline and have already requested more time! :)

Cheryl Crouse

I think your problem might be in the third IF statement where you want it to show Blue, you are using "IF(AND" without an "And" portion of the statement. Maybe try revising that portion to ...IF(Status14 = "Not Started", "Blue", "Yellow")))...?

Fletcher Boll

@Paul Newcome Help por favor! You seem to be the master helper in SS!

Cheryl Crouse

I think your problem might be in the third IF statement where you want it to show Blue, you are using "IF(AND" without an "And" portion of the statement. Maybe try revising that portion to ...IF(Status14 = "Not Started", "Blue", "Yellow")))...?

Fletcher Boll

@Cheryl Crouse ohh that could be it! I'll try!

I ended up waaay simplifying it and putting the isblank at the beginning and it worked.

=IF(ISBLANK(Completed@row), "", IF(Completed@row = "complete", "Green", IF(Completed@row = "in progress", "Yellow", IF(Completed@row = "delayed", "Red"))))

Thank you!

Cheryl Crouse

@Fletcher Boll That's great! And yes, much cleaner!

Good that you could get rid of the Today()>[End Date] portion too. We are having fits with trying to roll up counts from several sheets where Today() is used in a formula. The dashboards and roll up sheets give inaccurate counts if each sheet that uses the Today() formula isn't opened and saved before so it can update the date.. UGH!!! I REALLY hope they fix THAT!

Paul Newcome

@Fletcher Boll Haha. Thanks for the vote of confidence, but I am by no means a "master".

It looks like you were able to get a working solution before I got logged on for the day. Is it working the way you need it to?

In reference to your comment about putting the ISBLANK at the beginning and that got it working... Nested IF statements read from left to right and stop at the first true value. This can be leveraged in a few different ways. The two most immediate ways are:

1. As you have found, the priority of your IF's can be maintained by the order of them within the overall formula
2. You can simplify because if it makes it to the second IF, then it is automatically assumed the first IF is not true. This means you don't have to specify in the second one that Completed@row has to be text since it MUST be text if it isn't blank.

@Cheryl Crouse I completely understand your frustration with the TODAY function. Here's a quick tip that should help.

Create a sheet called "zzzTODAY". On that sheet create a date column called "TODAY". Make that column the leftmost column on the sheet. In row 1 enter =TODAY(). Now you have today's date in the top left corner of the sheet.

Now here is where this comes in handy. On every single other sheet where you want to use the TODAY function. Don't. Create a column called "Today". Make it a date type column. Use cell linking to link to the top left cell (where it defaults to anyway when initially creating the link) of the zzzTODAY sheet. Then in your formulas, instead of using TODAY(), you can use $Today$1. A cell reference instead of the function itself.

Then all you have to do is make sure the zzzTODAY sheet is activated daily, and the cell links will automatically pull through to all of your other sheets. Now you only have one sheet instead of a bunch to activate.

Note: I used the name "zzzTODAY" for a couple of reasons.

1. It means that sheet is most likely going to be at the bottom of the list since folders and workspaces are generally sorted alphabetically by default.
2. When you are creating the link, you can either scroll all the way to the bottom of the sheet list or just type in "zzz" in the search bar to very quickly locate your sheet.

Here are a few interesting tidbits about the TODAY function...

You do NOT have to OPEN a sheet to get it to update the TODAY function. You only need to ACTIVATE it. To activate a sheet other than opening it, you can have a cell link that updates on it. You can have a form entry. You can submit an Update Request.

Anything that changes the data on the sheet will activate it which will update the TODAY function.

Now we can get a little fancy and automate activating the sheet so you don't even have to do that much (since you are cell linking to it with your other sheets, those too will be updated at the same time).

There is a 3rd party app called Zapier. We will come back to this shortly. Lets start with some minor tweaks to your "zzzTODAY" sheet.

In the Primary Column, enter the formula of

=COUNTIFS($TODAY$1:TODAY@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

This will duplicate your row number. Make sure the first two rows have this formula in it so that atuofill can pick it up.

Now lets go back to Zapier. If this is the only thing you are using it for, you can set this up using the free plan.

First you are going to link your Zapier and Smartsheet together. From there you are going to create a "zap" that runs on a schedule. That schedule will be every day at 12:00am. Have it create a new row on your "zzzTODAY" sheet.

This creating a new row automatically at 12:00am will activate that sheet at that time. This will in turn update your TODAY function on that sheet which will in turn update all of your other sheets that are cell linked to it.

Now lets go back to our "zzzTODAY" sheet real quick to finish it off. Because we are adding a new row every day, and there is a row limit on a sheet of 5,000, eventually we are going to run out of room and need to delete old rows. Since you can get about 13 1/2 years out of 5,000 rows, let's go ahead and set up an Alert automation so that we don't forget.

Go into your Automations and set up an alert to trigger when the Primary Column equals 4,970. This will send you an alert that your sheet is almost at capacity about 30 days before it reaches that capacity.

And there you have it. An automated TODAY function that can be setup then forgotten about for the next 13+ years. Hopefully by then Smartsheet will have it's own automatically updating TODAY function and you won't need the Zapier setup anymore anyway.

Cheryl Crouse

Thanks @Paul Newcome ! I think I will try the zzzToday idea. Question, if I create an automated "request for update" to myself each morning in the zzzToday sheet, then I enter something in the grid each morning, will that activate the sheet and get it to update? I'm thinking that might be an easier way to not forget, and if I go on holiday, I can have it alert whomever is in my place?

Paul Newcome

The update request will definitely work so long as some data is changed. I had thought about this route for myself as well, but I needed something a little more "hands-off". The Zapier setup will run every day regardless of holiday, weekend, etc. You really can set it up and then forget about it for a few years. And if that is the only thing you are using Zapier for, then it can be done on the free plan!

Andrée Starå

Hi everyone,

I usually use Zapier in client solutions, but recently in an intake process, we used the TODAY function and linked it to individual project sheets through Sheet Summary.

Works perfectly as long as the Intake Sheet is saved.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Cheryl Crouse

Hi @Andrée Starå , what do you mean by you "...used the Today function and linked it to individual project sheets through Sheet Summary"? Is that something you did instead of setting up a zzzTODAY sheet?

Andrée Starå

@Cheryl Crouse Yes, exactly.

I added a column in the intake sheet, and each projects row is set to =TODAY(), so when the sheet is saved, it's changed to today's date, and then I use a VLOOKUP in each project sheet in the Sheet Summary section that gets updated in the background from the intake sheet.

Make sense?