Help

Re: IF/AND statement with Date TODAY

Solved
Jump to Solution
2871 2
cancel
Showing results for 
Search instead for 
Did you mean: 
J_JBA
5 - Automation Enthusiast
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
gwynn_kruger
6 - Interface Innovator
6 - Interface Innovator

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

Creates this result:

image

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:

image

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:

See Solution in Thread

3 Replies 3
gwynn_kruger
6 - Interface Innovator
6 - Interface Innovator

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

Creates this result:

image

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:

image

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:

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!

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