Link to home
Start Free TrialLog in
Avatar of MisUszatek
MisUszatekFlag 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!
Avatar of abhitrig
abhitrig
Flag of United States of America image

=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!

Avatar of MisUszatek

ASKER

Unfortunately something is missing. I got this error:

The formula contains a syntax error or is not supported.
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.
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
Avatar of abhitrig
abhitrig
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...

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