Testing multiple due dates against completion dates

Paul Foote

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.

Now the following formula works, but KILLS the performance.

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     

Andrée Starå

Hi Paul,

My first recommendation would be to update the formula to use the @row function 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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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.

Paul Foote

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

Paul Newcome

I am liking the new Automation updates. I'm still exploring the various possibilities, but it has definitely made life much easier.