IF Formula: Need one more layer to check alternate field for comprehensive If Formula

[Mackenzie_Sween]

Hi there!

I am needing help to add ONE layer to this IF formula.

It’s for an invoicing tracking base.

Overall, we are looking at 2 columns.

I found this super epic IF Formula here on the forum and need help.

The OUTCOME:
*This first part I need help with - If the Paid Date exists = PAID
(the rest, the formula I have works)
If the Invoice Due Date is same day = DUE TODAY
If the Invoice Due Date is within 5 days = PENDING
If the Invoice Due Date is 5+ days = UPCOMING
If the Invoice Due Date is past = PAST DUE

Here is a pic of the columns I am working with

Here is the formula I am working with, I am fairly sure I need to change the first layer??

IF (
{Due}="",
“Not Set”,
IF(
AND(
DATETIME_DIFF({Due},TODAY(),‘hours’) >= 12,
DATETIME_DIFF({Due},TODAY(),’days’) <= 5
),
“Due Soon”,
IF(
IS_BEFORE({Due},TODAY()),
“Past Due”,
IF (
IS_SAME({Due},TODAY()),
“Due Today”,
“Upcoming”
)
)
)
)

Thank you in advance for your GENIUS!!

[JonathanBowen · Accepted solution]

Hi @Mackenzie_Sweeney - try this:

IF(
  {Paid date}, 
  'PAID',
  IF(
    IS_BEFORE({Due Date},TODAY()),
    'PAST DUE',
    IF(
      IS_SAME({Due Date},TODAY()),
      'DUE TODAY',
      IF(
        DATETIME_DIFF({Due Date}, TODAY(), 'days') <= 5,
        'PENDING',
        IF(
          DATETIME_DIFF({Due Date}, TODAY(), 'days') > 5,
          'UPCOMING'
        )
      )
    )    
  )
)

I think your Pending/Due Soon formula is a bit more complicated than it needs to be, but perhaps theres some reason for that.

[Mackenzie_Sween]

Hey Jonathan - thank you so much for this reply.

(it’s a client request for the complex pending/due soon situation)

THIS is magical and exactly what I needed. I appreciate your genius & your time!