Skip to main content
Solved

IF/AND statement with Date TODAY

  • August 8, 2020
  • 3 replies
  • 41 views
J
Forum|alt.badge.img+2
  • J_JBA
  • Participating Frequently

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!

Best answer by gwynn_kruger

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

G
  • gwynn_kruger
  • Participating Frequently
  • Answer
  • August 9, 2020

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:

J
Forum|alt.badge.img+2
  • J_JBA
  • Author
  • Participating Frequently
  • August 9, 2020

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:


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
Forum|alt.badge.img+2
  • J_JBA
  • Author
  • Participating Frequently
  • August 9, 2020

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:


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

Terms & ConditionsAccessibility statement