Update invoice status using multiple IF statements

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!

Best Answer

Answers

  • Thanks, Andrée!

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

    Cheers!

    Mary

  • 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å
    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, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!