# IF/AND statement with Date TODAY

Topic Labels: Formulas
Solved
3131 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I’m trying to achieve the following:

If Invoice Status is ‘Paid’, then ‘Paid’
If Invoice Status is empty and the date in Payment Due is on or after today, then ‘Due’
If Invoice Status is empty and the date in Payment Due is before today, then ‘Overdue’

I have created this formula, which is not working (it does show ‘Paid’ but not ‘Due’ where applicable):

IF({Invoice Status} = ‘Paid’, ‘Paid’,
IF(AND({Invoice Status)} = BLANK()),IS_AFTER({Payment Due}, TODAY()), ‘Due’,
IF(AND({Invoice Status} = BLANK()),IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’)))

Any help would be much appreciated!

1 Solution

Accepted Solutions
6 - Interface Innovator

Using this formula:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’, ‘Due’))

Creates this result:

The above screenshot has 2 items with Overdue status. I wasn’t expecting the 8/8/2020 to be considered Overdue but the formula looks correct to me.

I changed the function to avoid using the TODAY() function and instead created a new field with today’s date. I updated the formula to:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due}, {Today}), ‘Overdue’, ‘Due’))

Which creates this result:

Notice how the second overdue has disappeared! I might be wrong but I’m guessing this is because the TODAY() function returns the date in GMT rather than your own timezone.

For testing I added some extra functions to have TODAY() return the date in my timezone (America/Central) and it seems to work for me.

Here’s the new formula – this should be pretty close to what you need:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due},DATETIME_FORMAT(SET_TIMEZONE(TODAY(), ‘America/Chicago’), ‘M/D/YYYY’)), ‘Overdue’, ‘Due’))

Here’s the list of timezones if you don’t want to use central:

3 Replies 3
6 - Interface Innovator

Using this formula:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’, ‘Due’))

Creates this result:

The above screenshot has 2 items with Overdue status. I wasn’t expecting the 8/8/2020 to be considered Overdue but the formula looks correct to me.

I changed the function to avoid using the TODAY() function and instead created a new field with today’s date. I updated the formula to:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due}, {Today}), ‘Overdue’, ‘Due’))

Which creates this result:

Notice how the second overdue has disappeared! I might be wrong but I’m guessing this is because the TODAY() function returns the date in GMT rather than your own timezone.

For testing I added some extra functions to have TODAY() return the date in my timezone (America/Central) and it seems to work for me.

Here’s the new formula – this should be pretty close to what you need:
IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due},DATETIME_FORMAT(SET_TIMEZONE(TODAY(), ‘America/Chicago’), ‘M/D/YYYY’)), ‘Overdue’, ‘Due’))

Here’s the list of timezones if you don’t want to use central:

5 - Automation Enthusiast

Thank you so much for your help and explanation Gwynn! Working with the Today field made it a lot easier and your formula works perfectly. :slightly_smiling_face: Thanks again!

5 - Automation Enthusiast

I changed the formula slightly because Due and Overdue seemed to be reversed, and I adapted it to my time zone. So this is what I ended up with:

IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(IS_BEFORE({Payment Due},DATETIME_FORMAT(SET_TIMEZONE(TODAY(), ‘Europe/Amsterdam’), ‘D/M/YYYY’)), ‘Due’, ‘Overdue’))