# 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"

/* 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!
abhitrig

=IF(Not(ISBlank([Product Accepted])), "Product Accepted",
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!

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 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?
abhitrig

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.