Testing multiple due dates against completion dates
Hey Community members
I have a sheet that is tracking reporting compliance. Most of the reports only have ONE due date (typically reported annually ie 30th June), but there is a number that requires monthly and quarterly reporting.
To allow our users to enter up to 12 dates for a reporting cycle we have 12 fields to capture these dates (ie [1st Date], [2nd Date], [3rd Date] etc). The users flags that the reported has been completed by entering the date of completion (ie [1st Completed], [2nd Completed], [3rd Completed] etc). This completion date is also used to calculate if the completion was before or after the due date.
I have found that the report builder has limited our ability to test all 12 dates against the 12 completion dates and therefore have resorted to creating a reference cell [Group Status] within the sheet to automatically identify if anything is overdue.
To help explain the process I will use a quarter reporting cycle as an example:
So we have a report that is due 31/3/19 (Q1), 30/06/19 (Q2), 30/09/19 (Q3) & 31/12/19 (Q4)
If we check to see if anything is overdue as at today (13/05/19), we are only interested if the 31/3/19 [1st Date] report has been completed or overdue if there is not completion date [1st Completed]. Down the track if the date was 3/11/19, then we need to ensure 31/3, 30/6 & 30/9 have been completed, by testing the values in [1st date] : [1st Completed] , [2nd date] : [2nd Completed], [3rd date] : [3rd Completed].
The following formula (below) is use to test for any overdue dates, if found the text “over” is appended to the cell [Group Status]. When reporting if [Group Status] contains “over”, then that row is included in the report.
现在下面的公式,但每杀死formance.
Formula in [Group status]
=IF(AND(AND(ISDATE([1st Date]1), [1st Date]1 < TODAY(), ISBLANK([1st Completed]1))), "1.Over", " 1.Not") + IF(AND(AND(ISDATE([2nd Date]1), [2nd Date]1 < TODAY(), ISBLANK([2nd Completed]1))), " 2.Over", " 2.Not") + IF(AND(AND(ISDATE([3rd Date]1), [3rd Date]1 < TODAY(), ISBLANK([3rd Completed]1))), " 3.Over", " 3.Not") + IF(AND(AND(ISDATE([4th Date]1), [4th Date]1 < TODAY(), ISBLANK([4th Completed]1))), " 4.Over", " 4.Not") + IF(AND(AND(ISDATE([5th Date]1), [5th Date]1 < TODAY(), ISBLANK([5th Completed]1))), " 5.Over", " 5.Not") + IF(AND(AND(ISDATE([6th Date]1), [6th Date]1 < TODAY(), ISBLANK([6th Completed]1))), " 6.Over", " 6.Not") + IF(AND(AND(ISDATE([7th Date]1), [7th Date]1 < TODAY(), ISBLANK([7th Completed]1))), " 7.Over", " 7.Not") + IF(AND(AND(ISDATE([8th Date]1), [8th Date]1 < TODAY(), ISBLANK([8th Completed]1))), " 8.Over", " 8.Not") + IF(AND(AND(ISDATE([9th Date]1), [9th Date]1 < TODAY(), ISBLANK([9th Completed]1))), " 9.Over", " 9.Not") + IF(AND(AND(ISDATE([10th Date]1), [10th Date]1 < TODAY(), ISBLANK([10th Completed]1))), " 10.Over", " 10.Not") + IF(AND(AND(ISDATE([11th Date]1), [11th Date]1 < TODAY(), ISBLANK([11th Completed]1))), " 11.Over", " 11.Not") + IF(AND(AND(ISDATE([12th Date]1), [12th Date]1 < TODAY(), ISBLANK([12th Completed]1))), " 12.Over", " 12.Not")
Hoping someone can help come up with a better solution
Comments
-
Andrée Starå ✭✭✭✭✭✭Hi Paul,
My first recommendation would be to update the formula to use the@rowfunction because then you won't need to think about the row numbers and it's a lot less performance heavy.
More info:https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#row
Let me know how it works for you?
I'd be happy to take a further look if needed to see what else could be more efficient.
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,[email protected])
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭I feel like a formula to pull the last/most recent date in the row and using that for comparison would be much more efficient.
I feel like breaking this out into a few helper columns for each part and then joining them together in the Group Status column would also be much easier.
Andree is correct though. Being able to see your setup would be immensely helpful.
-
Hi Paul
Thanks so much for you info/comment.
Currently taking another track and seeing what Automation/Workflows can do
Cheers
PaulF
-
Andrée Starå ✭✭✭✭✭✭
Did you see a big difference in performance with my suggested simplification of the formula?
I can see that it could work with the new Automation instead.
Let us know how it goes and if we can help in any way!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
I am liking the new Automation updates. I'm still exploring the various possibilities, but it has definitely made life much easier.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
