Nested IF Help!

JKP0009

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!

Carson Penticuff

I took a few liberties with this. I also was not sure if you "never" wanted a 0 as your result, or only in certain cases. This should replace any 0 with 1.

=IF([IT Ref #]@row = "", IF([Completion Date]@row = "", IF(NETDAYS([Created Date]@row, TODAY()) = 0, 1, NETDAYS([Created Date]@row, TODAY(1)), IF(NETDAYS([Created Date]@row, [Completed Date]@row) = 0, 1, NETDAYS([Created Date]@row, [Completed Date]@row)))IF(NETDAYS([Created Date]@row, [Agility Creation Date]@row) = 0, 1, NETDAYS([Created Date]@row, [Agility Creation Date]@row)))

JKP0009

Thanks Carson! Quick question, in the highlighted section below, should there be a comma where I have indicated in red?

Carson Penticuff

Good catch, there most definitely should be a comma there!

JKP0009

I'm getting an #INCORRECT ARGUEMENT error when I add in the comma. The colors really help me try and see what's going on, but with the number of IFs in this, I'm getting lost!

JKP0009

@Carson Penticuff I think there is a green parens missing after TODAY(1)) (see below for correction). I think that holds the correct logic for what we are trying to do, but double check me?

Carson Penticuff

That was likely because I missed a closing parenthesis...

=IF([IT Ref #]@row = "", IF([Completion Date]@row = "", IF(NETDAYS([Created Date]@row, TODAY()) = 0, 1, NETDAYS([Created Date]@row, TODAY(1))), IF(NETDAYS([Created Date]@row, [Completed Date]@row) = 0, 1, NETDAYS([Created Date]@row, [Completed Date]@row))), IF(NETDAYS([Created Date]@row, [Agility Creation Date]@row) = 0, 1, NETDAYS([Created Date]@row, [Agility Creation Date]@row)))

Carson Penticuff

Yes, you are correct. I posted the previous comment before I saw yours.

JKP0009

Perfect. Thank you so much Carson!

I just turned this into a Column formula and I see that most (if not all) values are off by 1 day for this scenario:

IT Ref # is Blank, Created Date is 6/19/23, Status is Open

In this scenario, we would want to return a value of 1 (TODAY() - [Created Date] = 0, but return 1).

Should the formula read?

Carson Penticuff

Yes, you are correct. I forgot to remove the 1 when I was modifying something.

JKP0009

Thank you so much Carson!! I appreciate it! You've been very helpful today!!!

Carson Penticuff

I'm glad I can help. You may want to paste the final version of the formula you are using here, just for reference, in case anyone stumbles on this thread looking for assistance later.