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

Topic Labels: Formulas
Solved
402 2
cancel
Showing results for
Did you mean:  4 - Data Explorer

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”
)
)
)
)

1 Solution

Accepted Solutions  13 - Mars

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.

2 Replies 2  13 - Mars

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.  4 - Data Explorer

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! 