Skip to main content

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!!

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.

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!

Reply


Terms & Conditions