Avatar of MisUszatek
MisUszatek
Flag for United States of America asked on

Another calculated column formula.

@abhitrig

I need a formula that depending on the value of the date fields displays some text. The dates happen in sequence. Here is how it should look like:

- IF [Product Accepted] => "Product Accepted"
- IF [Product Received] => "Product Received"

/* this is the fun part/
- IF [Product Expected]
-- ([Product Expected]-Today) < 7 => "Product Expected"
-- ([Product Expected]-Today) between 7 and 3  => "Due Soon"
-- ([Product Expected]-Today) between 3 and 0 => "Due Now"
-- ([Product Expected]-Today) > 0 => "Past Due"

- IF [Product Ordered] => "Product Ordered"

- ELSE => "Not ordered" (when all dates are empty)

Thanks!
Microsoft SharePointProgramming

Avatar of undefined
Last Comment
abhitrig

8/22/2022 - Mon
abhitrig

=IF(Not(ISBlank([Product Accepted])), "Product Accepted",
 IF(Not(ISBlank([Product Received])), "Product Received",
 IF(Not(ISBlank([Product Ordered])), "Product Ordered",
 IF(Not(ISBlank([Product Expected])),
   IF(AND(([Product Expected]-Today) >3,([Product Expected]-Today) <7)),"Due Soon",
   IF(AND(([Product Expected]-Today) >0,([Product Expected]-Today) <3)),"Due Now",
   IF(([Product Expected]-Today) < 7,"Product Expected",
   IF(([Product Expected]-Today) <0,"Past Due","NA"))))),"Not ordered"))))

I hope i did not miss any brackets!

MisUszatek

ASKER
Unfortunately something is missing. I got this error:

The formula contains a syntax error or is not supported.
abhitrig

Try this...
=IF(NOT(ISBLANK([Product Accepted])), "Product Accepted",
IF(NOT(ISBLANK([Product Received])), "Product Received",
 IF(NOT(ISBLANK([Product Ordered])), "Product Ordered",
IF(NOT(ISBLANK([Product Expected])),(
IF(AND(([Product Expected]-Today) >3,([Product Expected]-Today) <7),"Due Soon",
IF(AND(([Product Expected]-Today) >0,([Product Expected]-Today) <3),"Due Now",
IF(([Product Expected]-Today) < 7,"Product Expected",
IF(([Product Expected]-Today) <0,"Past Due","NA")
  )
            )
               )
       ),"Not Ordered"))
     ) )

Debugging such a long formula is always a pain.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
MisUszatek

ASKER
It works now but shows Product Ordered for any Product Expected situation. I think this is becasue the date for Product Ordered is always present when the Product is Expected so it should be AFTER the Product Expected formula. Makes sense?
ASKER CERTIFIED SOLUTION
abhitrig

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MisUszatek

ASKER
Almost there... :)

It was showing NA for values over 7 but I guess it was becasue of <7. I Changed it to >7 and it works. But there is another problem. When the day count is exactly 7, 3 or 0 it shows NA...

abhitrig

That should be easy, set > to >= and < to <= where necessary.

Or change the values to 8, 2(or 4), and -1 (or 1) for 7,3 or 0 depending on whether its greater than or less than
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.