Skip to main content
Solved

AND OR + IS BEFORE IF statement help

M

Hi there! I’m trying to do a fairly simple formula but it has quite a lot of conditions. I’m a beginner and not sure where I’m going wrong, could someone help?

The board is a basic project overview. I’m trying to set a column to display “on track” or “overdue” depending on what the due date and status of the item is.

My current formula is written like so:

IF(
AND(IS_BEFORE(Due,TODAY(),
IF(
OR(
Status = “In Progress”,
Status = “With Client”,
Status = “Not yet started”,
),
“Overdue!”,
“On track”
)))

Any help would be greatly appreciated!

Meri

Best answer by augmented

Meri_Wilson wrote:

Hi augmented, thank you so much!
That is exactly what I’m attempting yes.
Unfortunately this is still giving me an error saying invalid formula - any ideas? I also checked the capitalisation of the Status options in case that was messing with things, and removed some spaces but that didn’t help. Been banging my head against this for ages!


My fault Meri. There’s an extra comma after ‘Not yet started’ that needs to be removed. :man_facepalming:

View original
Did this topic help you find an answer to your question?

4 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • October 7, 2021

Hi Meri. I gather than you want to check for two things being true: 1) due date is in the past and 2) some status is one of three items. If so, the task/proj is overdue. First problem I saw is that your IS_BEFORE is not closed off with a needed right paren. Then, you don’t need the second if. Try this…

IF(
AND(
   IS_BEFORE(Due,TODAY()),
   OR(
    Status = 'In Progress',
    Status = 'With Client',
    Status = 'Not yet started',
   )
),
'Overdue!',
'On track'
)
M
  • Meri_Wilson
  • Author
  • New Participant
  • 3 replies
  • October 7, 2021
augmented wrote:

Hi Meri. I gather than you want to check for two things being true: 1) due date is in the past and 2) some status is one of three items. If so, the task/proj is overdue. First problem I saw is that your IS_BEFORE is not closed off with a needed right paren. Then, you don’t need the second if. Try this…

IF(
AND(
   IS_BEFORE(Due,TODAY()),
   OR(
    Status = 'In Progress',
    Status = 'With Client',
    Status = 'Not yet started',
   )
),
'Overdue!',
'On track'
)

Hi augmented, thank you so much!
That is exactly what I’m attempting yes.
Unfortunately this is still giving me an error saying invalid formula - any ideas? I also checked the capitalisation of the Status options in case that was messing with things, and removed some spaces but that didn’t help. Been banging my head against this for ages!

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • Answer
  • October 7, 2021
Meri_Wilson wrote:

Hi augmented, thank you so much!
That is exactly what I’m attempting yes.
Unfortunately this is still giving me an error saying invalid formula - any ideas? I also checked the capitalisation of the Status options in case that was messing with things, and removed some spaces but that didn’t help. Been banging my head against this for ages!


My fault Meri. There’s an extra comma after ‘Not yet started’ that needs to be removed. :man_facepalming:

M
  • Meri_Wilson
  • Author
  • New Participant
  • 3 replies
  • October 7, 2021
augmented wrote:

My fault Meri. There’s an extra comma after ‘Not yet started’ that needs to be removed. :man_facepalming:


Ah perfect! Thank you so much for your help, and this has helped me understand the syntax much better!

Reply


Most helpful members this week

Terms & Conditions