Hi all!
I'm working on a formula that will give me the number of days a work item has been processing with our support team. I've identified the following variables:
1.) When {IT Ref #} is not blank, then I want to subtract two date columns ({Agility Creation Date} - {Created Date}
2.) When {IT Ref #} is blank, then I want to perform 1 of two calculations, depending on whether the work item is still open or closed
2.a) When {IT Ref #} is blank and the work item is open, I want to subtract a date column from the TODAY function (TODAY() - {Created Date}
2.b) When {IT Ref #} is blank and the work item is closed, I want to subtract two date columns ({Completion Date} - {Created Date}
The idea here is to get the number of days that a work item was with the support team before being closed out by them or before sending the work item to our IT group.
I was working with a Smartsheet Pro in a Pro Desk session but we ended up running out of time before completing. This is the formula that we ended up with, which is pretty close. However, the issue with this formula is that in the [value_if_false] section of the function, I need to include the same logic that when a value of 0 is calculated to return the number 1. The logic behind this is that if the support team closes out a work item on the same date it was submitted, there is a single day of work that need to be accounted for, instead of 0.
=IF(IF(NOT(ISBLANK([IT Ref #]@row)), [Agility Creation Date]@row - [Created Date]@row, [Completion Date]@row - [Created Date]@row) = 0, 1, [Agility Creation Date]@row - [Created Date]
One idea that I had was to have separate columns for each calculation and run a MAX formula in a separate column, but it seems like there is a more efficient way to do this. Thank you all so much for your help. Looking forward to any solutions anyone has!
