Does this community have a bank of shared formulas, because this might be helpful to someone
This formula is very straightforward but it took more than an hour to build, so maybe I can save you some time!
This formula looks at the field "Dash Help Due Date" and returns one of five things:
No Date if blank
This item was due x days ago
This item was due yesterday
This item is due today
This item is due tomorrow
This item is due in in x days, on [Day of the Week], [Month], [Day] (eg Friday, February 6)
=IF(ISBLANK([Dash Help Due Date]@row), "No Date", (IF(AND((([Dash Help Due Date]@row - TODAY()) < 0), ABS([Dash Help Due Date]@row - TODAY()) > 1), "This item is was due " + ABS([Dash Help Due Date]@row - TODAY()) + " " + IF((ABS([Dash Help Due Date]@row - TODAY())) = 1, "day", "days") + " ago", (IF(AND((([Dash Help Due Date]@row - TODAY()) < 0), ABS([Dash Help Due Date]@row - TODAY()) = 1), "This item was due yesterday", (IF(AND((([Dash Help Due Date]@row - TODAY()) = 0), ABS([Dash Help Due Date]@row - TODAY()) = 0), "This item is due today", (IF(AND((([Dash Help Due Date]@row - TODAY()) > 0), ABS([Dash Help Due Date]@row - TODAY()) = 1), "This item is due tomorrow", (IF(AND((([Dash Help Due Date]@row - TODAY()) > 0), ABS([Dash Help Due Date]@row - TODAY()) > 1), "This item is due in " + ABS([Dash Help Due Date]@row - TODAY()) + " days, on " + IF(WEEKDAY([Dash Help Due Date]@row) = 1, "Sunday", IF(WEEKDAY([Dash Help Due Date]@row) = 2, "Monday", IF(WEEKDAY([Dash Help Due Date]@row) = 3, "Tuesday", IF(WEEKDAY([Dash Help Due Date]@row) = 4, "Wednesday", IF(WEEKDAY([Dash Help Due Date]@row) = 5, "Thursday", IF(WEEKDAY([Dash Help Due Date]@row) = 6, "Friday", IF(WEEKDAY([Dash Help Due Date]@row) = 7, "Saturday"))))))) + ", " + IF(MONTH([Dash Help Due Date]@row) = 1, "January", IF(MONTH([Dash Help Due Date]@row) = 2, "February", IF(MONTH([Dash Help Due Date]@row) = 3, "March", IF(MONTH([Dash Help Due Date]@row) = 4, "April", IF(MONTH([Dash Help Due Date]@row) = 5, "May", IF(MONTH([Dash Help Due Date]@row) = 6, "June", IF(MONTH([Dash Help Due Date]@row) = 7, "July", IF(MONTH([Dash Help Due Date]@row) = 8, "August", IF(MONTH([Dash Help Due Date]@row) = 9, "September", IF(MONTH([Dash Help Due Date]@row) = 10, "October", IF(MONTH([Dash Help Due Date]@row) = 11, "November", IF(MONTH([Dash Help Due Date]@row) = 12, "December")))))))))))) + " " + WEEKDAY([Dash Help Due Date]@row), 999)))))))))))
Answers
-
There isn't necessarily a shared bank of formulas out there that encompasses everything, but I have a thread that focuses on time based formulas and @L@123 has a thread that focuses on date based formulas.
Taking a look at yours, it honestly looks like you may have "over-written" it as in there are some parts that are not needed (and I noticed a lot of extra parenthesis as well).
For example, your portion for "This item was due x days ago" includes calculations for "1 day ago", but that is then replicated for "yesterday". Based on that, your formula will never output "yesterday" as "1 day ago" will be the first true value. One makes the other redundant. I also noticed that you used a WEEKDAY function there at the end when I believe you should be using a DAY function. The WEEKDAY function will only populate 1 - 7, so if the due date is on the 27th of February it will only populate "7" instead of "27".
You can also greatly simplify your formula by building out a table and referencing it with an INDEX/MATCH to populate Sunday - Saturday and January - December.
To simplify your formula a little bit without referencing tables, I personally would rewrite it as:
=IF([Dash Help Due Date]@row = "", "No Date", IF([Dash Help Due Date]@row - TODAY() < -1, "This item is was due " + ABS([Dash Help Due Date]@row - TODAY()) + " days ago", IF(Dash Help Due Date]@row - TODAY() = -1, "This item was due yesterday", IF([Dash Help Due Date]@row = TODAY(), "This item is due today", IF([Dash Help Due Date]@row - TODAY() = 1, "This item is due tomorrow", IF([Dash Help Due Date]@row - TODAY() > 1, "This item is due in " + [Dash Help Due Date]@row - TODAY() + " days, on " + IF(WEEKDAY([Dash Help Due Date]@row) = 1, "Sunday", IF(WEEKDAY([Dash Help Due Date]@row) = 2, "Monday", IF(WEEKDAY([Dash Help Due Date]@row) = 3, "Tuesday", IF(WEEKDAY([Dash Help Due Date]@row) = 4, "Wednesday", IF(WEEKDAY([Dash Help Due Date]@row) = 5, "Thursday", IF(WEEKDAY([Dash Help Due Date]@row) = 6, "Friday", IF(WEEKDAY([Dash Help Due Date]@row) = 7, "Saturday"))))))) + ", " + IF(MONTH([Dash Help Due Date]@row) = 1, "January", IF(MONTH([Dash Help Due Date]@row) = 2, "February", IF(MONTH([Dash Help Due Date]@row) = 3, "March", IF(MONTH([Dash Help Due Date]@row) = 4, "April", IF(MONTH([Dash Help Due Date]@row) = 5, "May", IF(MONTH([Dash Help Due Date]@row) = 6, "June", IF(MONTH([Dash Help Due Date]@row) = 7, "July", IF(MONTH([Dash Help Due Date]@row) = 8, "August", IF(MONTH([Dash Help Due Date]@row) = 9, "September", IF(MONTH([Dash Help Due Date]@row) = 10, "October", IF(MONTH([Dash Help Due Date]@row) = 11, "November", IF(MONTH([Dash Help Due Date]@row) = 12, "December")))))))))))) + " " + DAY([Dash Help Due Date]@row), 999))))))
So aside from the WEEKDAY/DAY adjustment and using the nested IF's for weekday and month, the changes would take this portion of your formula from
=IF(ISBLANK([Dash Help Due Date]@row), "No Date", (IF(AND((([Dash Help Due Date]@row - TODAY()) < 0), ABS([Dash Help Due Date]@row - TODAY()) > 1), "This item is was due " + ABS([Dash Help Due Date]@row - TODAY()) + " " + IF((ABS([Dash Help Due Date]@row - TODAY())) = 1, "day", "days") + " ago", (IF(AND((([Dash Help Due Date]@row - TODAY()) < 0), ABS([Dash Help Due Date]@row - TODAY()) = 1), "This item was due yesterday", (IF(AND((([Dash Help Due Date]@row - TODAY()) = 0), ABS([Dash Help Due Date]@row - TODAY()) = 0), "This item is due today", (IF(AND((([Dash Help Due Date]@row - TODAY()) > 0), ABS([Dash Help Due Date]@row - TODAY()) = 1), "This item is due tomorrow", (IF(AND((([Dash Help Due Date]@row - TODAY()) > 0), ABS([Dash Help Due Date]@row - TODAY()) > 1), "This item is due in " + ABS([Dash Help Due Date]@row - TODAY()) + " days, on " +
to
=IF([Dash Help Due Date]@row = "", "No Date", IF([Dash Help Due Date]@row - TODAY() < -1, "This item is was due " + ABS([Dash Help Due Date]@row - TODAY()) + " days ago", IF(Dash Help Due Date]@row - TODAY() = -1, "This item was due yesterday", IF([Dash Help Due Date]@row = TODAY(), "This item is due today", IF([Dash Help Due Date]@row - TODAY() = 1, "This item is due tomorrow", IF([Dash Help Due Date]@row - TODAY() > 1, "This item is due in " + [Dash Help Due Date]@row - TODAY() + " days, on " +
That's a difference of 351 characters according to the character count in Microsoft word (plus an additional four characters removed for the WEEKDAY/DAY change).
If we build out a table like so...
We can take the nested IF's for the weekday from
IF(WEEKDAY([Dash Help Due Date]@row) = 1, "Sunday", IF(WEEKDAY([Dash Help Due Date]@row) = 2, "Monday", IF(WEEKDAY([Dash Help Due Date]@row) = 3, "Tuesday", IF(WEEKDAY([Dash Help Due Date]@row) = 4, "Wednesday", IF(WEEKDAY([Dash Help Due Date]@row) = 5, "Thursday", IF(WEEKDAY([Dash Help Due Date]@row) = 6, "Friday", IF(WEEKDAY([Dash Help Due Date]@row) = 7, "Saturday")))))))
and shrink it down to
INDEX({Weekday}, MATCH(WEEKDAY([[Dash Help Due Date]@row), {Number}, 0))
Then take the nested IF's for the month
IF(MONTH([Dash Help Due Date]@row) = 1, "January", IF(MONTH([Dash Help Due Date]@row) = 2, "February", IF(MONTH([Dash Help Due Date]@row) = 3, "March", IF(MONTH([Dash Help Due Date]@row) = 4, "April", IF(MONTH([Dash Help Due Date]@row) = 5, "May", IF(MONTH([Dash Help Due Date]@row) = 6, "June", IF(MONTH([Dash Help Due Date]@row) = 7, "July", IF(MONTH([Dash Help Due Date]@row) = 8, "August", IF(MONTH([Dash Help Due Date]@row) = 9, "September", IF(MONTH([Dash Help Due Date]@row) = 10, "October", IF(MONTH([Dash Help Due Date]@row) = 11, "November", IF(MONTH([Dash Help Due Date]@row) = 12, "December"))))))))))))
and replace that with
INDEX({Month}, MATCH(MONTH([Dash Help Due Date]@row, {Number}, 0))
which when coupled with the other simplifications brings the entire formula down to:
=IF([Dash Help Due Date]@row = "", "No Date", IF([Dash Help Due Date]@row - TODAY() < -1, "This item is was due " + ABS([Dash Help Due Date]@row - TODAY()) + " days ago", IF(Dash Help Due Date]@row - TODAY() = -1, "This item was due yesterday", IF([Dash Help Due Date]@row = TODAY(), "This item is due today", IF([Dash Help Due Date]@row - TODAY() = 1, "This item is due tomorrow", IF([Dash Help Due Date]@row - TODAY() > 1, "This item is due in " + [Dash Help Due Date]@row - TODAY() + " days, on " + INDEX({Weekday}, MATCH(WEEKDAY([[Dash Help Due Date]@row), {Number}, 0)) + ", " + INDEX({Month}, MATCH(MONTH([Dash Help Due Date]@row, {Number}, 0)) + " " + DAY([Dash Help Due Date]@row), 999))))))
If you include the character count for building the table out, that is still taking your 1,622 character formula and shrinking it down to 838 characters. That's almost half the characters. A lot less typing. Less work for the sheet to do. And much easier to manage if you need to adjust anything later down the road.
-
And while @Paul Newcome has simplified your formula for you quite a bit, personally I would split it up among different columns. It makes it easier to read, and much easier to filter for future analysis. It also makes formula generation much simpler. You can even put in a concatenate to bring it together for the full result from your original problem, then build in shared filters from other individual columns to direct users to specific information if you want.
-
Thanks for this feedback! It's very interesting. In reviewing my work I found a typo in the "This item is was due " string. Obviously this needs to be just "was."
Everything you said makes sense, thank you for the thoughtful analysis.
On the item that is in the past, My forumla works because I constrain the number of days using AND, but I see where putting them in the correct order lets me cut down on the complexity of the IF statement by taking out the AND function. I am going to make this change.
As for the use of DAY, I used DAY because it returns 1-7, which is what I need to identify the name of each day. Which is what I need.
If I want to set up the table to cross-reference months and days, is that another smartsheet object, which will require a cross-sheet reference? And is that more efficient than having more characters in the formula but having all of the necessary data on board? I do like the idea of having a set of helper tables that I reference from other sheets, but I have not been in this long enough to evaluate whether that's helpful.
-
That is definitely how I built it - put each element in its own column and then brought it all together. So If I do need to make a change I can go to the planning sheet, change a column, test it all out, and then update the full thing.
If this were Excel, where I can copy and paste columns with their names and content into other worksheets, I would have multiple columns and built the formula using references, but I haven't seen that Smartsheet can do that.
Can Smartsheet do that? Can I copy five columns from one sheet into another?
-
You could set the table up on the same sheet or on a different sheet and use cross sheet references. I personally use a different sheet that has a bunch of different tables in it so that I can just reference that one "Table Sheet" in all of my other sheets. It saves me from having to build out the same tables over and over again for each instance.
The WEEKDAY/DAY change I mentioned was in reference to the very end of your formula.
"This item is due in in x days, on [Day of the Week], [Month], [Day] (eg Friday, February 6)"
In your formula, the bold portion is being populated by a WEEKDAY function instead of the DAY function.
Help Article Resources
Categories
Check out the Formula Handbook template!
