How can i hide negative numbers in the following counting formula

julieg

My formula counts number of days past Due Date if no Completion Date, but is returning negative values for any task that is not past the Due Date.

=IF(ISDATE([Completion Date]@row), "", TODAY() - [Due Date]@row)

Any help is greatly appreciated!

julieg

Hi Brad!,

Yep, that fixed it perfectly. Thank you so much for your patience and assistance. It is interesting to me that there does not appear to be a way to stop counting a number when using TODAY().

Please take care Brad,

Julie

MVP OPS

hi @julieg

This formula is saying if completion date field is a date, then days over due to completion is "blank", if NOT, Todays date - Due Date, which is giving you the negatives.

I thin this formula will solve your use case.

=if(ISDATE([Completion Date]@row), "",if(([due date]@row-today())<=0,"", today()-[due date]@row

Hope this helps, have a good night.

julieg

Hello Brad!

thanks so much for responding. I did try your formula, this is what I tried:

=IF(ISDATE([Completion Date]@row), "", IF(([Due Date]@row - TODAY()) <= 0, "", TODAY() - [Due Date]@row))

If you reference the screen shot above, all of the negative values are still present for any task that is not Overdue (past the Due Date). Essentially everything in the screen shot above is exactly the same except now, the actual Overdue item is showing blank, instead of the days past due.

The formula above when applied to a task that is Overdue (past the Due Date) now shows blank instead of the number of days past due. So the logic seems to be reversed. The Overdue value should show, and the negative values should be blank. I hope that makes since.

Anything we can do to adjust the formula to show the days past due for a task Overdue (past the Due date), and then hide the negative values from the formula for rows that are not past due?

Thanks so much for your help,

Julie

julieg

Hi Brad,

Also, wondering if we can show blank in the Days Past Due column if completion date is not blank. While we can't stop the formula from counting, we can at least hide the value when Completion Date is not blank.

Thanks again for all of your time and help!

Julie

julieg

Hi Brad,

My messages above are probably a little confusing. Here is what I've determined. The following formula is working fine, except, it is displaying negative numbers when a task is not past due.

=IF(ISDATE([Completion Date]@row), "", TODAY() - [Due Date]@row)

If we can solve the negative numbers - I think we are good! Please let me know if you have any ideas.

Thanks!

MVP OPS

Hi Julie,

The issue with the original formula is that it will continually run the formula if the completion date has not been input. Nesting the if statements should stop the inputting. I had my today and due date math switched on original, sorry about that. Let me know how this one goes.

=IF(ISDATE([Completion Date]@row), "", IF((today()-[due date]@row) <= 0, "", if( TODAY()-[Due Date]@row>=0, TODAY() - [Due Date]@row

julieg

Hi Brad!,

Yep, that fixed it perfectly. Thank you so much for your patience and assistance. It is interesting to me that there does not appear to be a way to stop counting a number when using TODAY().

Please take care Brad,

Julie