Past due formula keeps providing #INVALID DATA TYPE error

Jenna2424

Hello,

After much research and experimenting, I am still stuck on a formula for my status log. I am trying to create a formula that counts how many business days a task is past due. Therefore, the formula must check the due date, closed date, and status columns.

The formula currently reads as this: =IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "Closed", "Closed", IF(NETWORKDAYS([Due Date]@row, [Closed Date]@row - 1) < 1, NETWORKDAYS([Due Date]@row, [Closed Date]@row))))

Any help / revisions would be much appreciated!!

Thank you.

KPH

The part in bold is checking there is a due date and the status is closed. Is that the only requirement you have to calculate the days - there must be a due date and the status must not be closed?

=IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "Closed", "Closed", IF(NETWORKDAYS([Due Date]@row, [Closed Date]@row - 1) < 1, NETWORKDAYS([Due Date]@row, [Closed Date]@row))))

If so, then you do not need another IF, you can simply put the net work days in to be evaluated when both of the IFs are false:

=IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "Closed", "Closed", NETWORKDAYS([Due Date]@row, [Closed Date]@row)))

jmyzk_cloudsmart_jp

Hi @KPH

I would compare the Due Date with today to count the number of business days a task is past due.

As you want to indicate as "Closed" if the status is "closed", and if the [Closed Date]@row is not blank, it does not make sense to calculate NETWORKDAYS([Due Date]@row, [Closed Date]@row). If the [Closed Date]@row is not blank, you just need to return "Closed".

Instead of manually letting users enter the "Closed Date", I would lock the "Closed Date" column and use automation like the one below to record the date when the status gets changed to "closed'.

Then, the past due column formula is as follows;

=IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "closed", "Closed ", IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY()) + " days past due", "")))

If you do not want to use the automation, you can modify the formula like this;

=IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(OR(Status@row = "closed", ISDATE([Closed Date]@row)), "Closed ", IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY()) + " days past due", "")))

https://app.smartsheet.com/b/publish?EQBCT=8542eeee5bf3444ea88bc80931634755

KPH

Hi @jmyzk_cloudsmart_jp

I interpreted the question as Jenna having two columns - Due Date and Closed Date - and wanting to calculate the business days between them if the Status was not closed and due date was not blank.

@Jenna2424 - now you have a few different options to play with. Enjoy! Let us know if you have any follow up questions.

jmyzk_cloudsmart_jp

Hi @KPH

That's a good clarification. When tasks are "done but overdue," calculating the difference between the due date and the closed date using NETWORKDAYS provides meaningful insight into how many business days the task was overdue.😁

To handle both scenarios where a task might be ongoing or closed and still provide past-due data, @Jenna2424 could use a combined approach:

=IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "closed", "Closed " + IF(NETWORKDAYS([Due Date]@row, [Closed Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Closed Date]@row) + " days past due"), IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY()) + " days past due", "")))

https://app.smartsheet.com/b/publish?EQBCT=8542eeee5bf3444ea88bc80931634755

Jenna2424

@jmyzk_cloudsmart_jp Thanks so much for the automation tip - I copied your screenshot exactly to create it, but upon running it, it then populates the closed date as today's date for every single item, not just those with a status as "Closed". I double checked my automation several times, deleted and tried again, etc. Would you know what the issue is here?

jmyzk_cloudsmart_jp

@Jenna2424

Since the workflow populates the closed date as today's date for every item, the action part is working fine.

So, the problem should be at the trigger part.

If you want me to share the demo sheet, contact me at jmyzk@cloudsmart.jp.

Jenna2424

@jmyzk_cloudsmart_jp and @KPH Thank you both SO much!! I got the formula up and running. Cannot thank you both enough for the help. This is the formula that ended up working: =IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "closed", "Closed " + IF(NETWORKDAYS([Due Date]@row, [Closed Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Closed Date]@row)), IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY())))).

One last question - any way to add in a clause wherein if it is not past due, it enters a 0 into the column instead of leaving the cell blank? Thank you!

Jenna2424

@jmyzk_cloudsmart_jp and @KPH Thank you both SO MUCH!! The formula is up and running! Cannot thank you both enough for the help.

This is the formula that ended up working: =IF(ISBLANK([Due Date]@row), "Date Not Provided", IF(Status@row = "closed", "Closed " + IF(NETWORKDAYS([Due Date]@row, [Closed Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Closed Date]@row)), IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY())))).

One last question: Is there any clause we could potentially add that would insert a 0 in the column cell if the item is not past due? Instead of having the cell be blank? Thank you!

KPH

Hi @Jenna2424

Yes, you can. Each IF function has the option to include a value (or formula) to return if false.

At the moment your IF looks like this:

=IF(logic, value-if-true)

You can do this

=IF(logic, value-if-true, value-if-false)

You do this with your first IF, you have "Date Not Provided" if the logic is true and then the second IF is evaluated if the first is false (see how it appears after a comma).

In the IF statement that evaluates if the date is past due, you can add a comma after the thing to do if it is past due and then a zero to return 0 if false:

=IF(NETWORKDAYS([Due Date]@row, TODAY()) > 0, NETWORKDAYS([Due Date]@row, TODAY()) ,0)