IF/AND statement with Date TODAY

[J_JBA]

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!

[gwynn_kruger · Accepted solution]

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:

Airtable - Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

[J_JBA]

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!

[J_JBA]

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