Update invoice status using multiple IF statements

Mary Smith

Hi there!

I have the following columns: Actual Invoice Date, Due Date, Invoice Status and, Days Past Due.

First, I'd like to have the Invoice Status display SENT if there is a date present the Invoice Date column and then have it display PAST DUE if the date in the Due Date column is greater than today's date.

I know these two statement work, but don't know how to combine them:

=IF(OR(ISDATE([Actual Invoice Date]3), "SENT", “”)

= IF([Due Date]3 < TODAY(), "PAST DUE", “”)

Second, I'd like to calculate the day past due IF the invoice is Past Due in the [Days Past Due] column.

Any help would be greatly appreciated!

Andrée Starå

Hi Mary,

Try something like this.

In the Invoice Status Column

=IF(AND(ISDATE([Due Date]@row); [Due Date]@row < TODAY()); "PAST DUE"; IF(ISDATE([Invoice Date]@row); "SENT"))

The same version but with the below changes for your and others convenience.

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row < TODAY()), "PAST DUE", IF(ISDATE([Invoice Date]@row), "SENT"))

In the Days Past Due

=IF(AND(ISDATE([Due Date]@row); [Due Date]@row < TODAY()); NETDAYS([Due Date]@row; TODAY() 1))

The same version but with the below changes for your and others convenience.

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row < TODAY()), NETDAYS([Due Date]@row, TODAY() 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Mary,

Try something like this.

In the Invoice Status Column

=IF(AND(ISDATE([Due Date]@row); [Due Date]@row < TODAY()); "PAST DUE"; IF(ISDATE([Invoice Date]@row); "SENT"))

The same version but with the below changes for your and others convenience.

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row < TODAY()), "PAST DUE", IF(ISDATE([Invoice Date]@row), "SENT"))

In the Days Past Due

=IF(AND(ISDATE([Due Date]@row); [Due Date]@row < TODAY()); NETDAYS([Due Date]@row; TODAY() 1))

The same version but with the below changes for your and others convenience.

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row < TODAY()), NETDAYS([Due Date]@row, TODAY() 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mary Smith

Thanks, Andrée!

I get an #UNPARESABLE error. But I think I can toy around with it until I get it.

Cheers!

Mary

Mary Smith

Ok, thought I had it.

Here's what I want to achieve:

If date is present in Actual Invoice Date column, then display SENT in Invoice Status Column

If date in Due Date column is greater than Today, then display PAST DUE in Invoice Status column

If checkbox in the PAID? Column is checked, then display PAID in Invoice Status column

I almost have it, except its displays SENT automatically when there is no date present. I need to figure out how to achieve the above only when there is date present in Actual Invoice column.

I know I can use the ISDATE funtion, but not sure how to combine them.

Any ideas?

Andrée Starå

Ok.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)