MisUszatek

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!

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!

ASKER

Unfortunately something is missing. I got this error:

The formula contains a syntax error or is not supported.

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.

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Almost there... :)

It was showing NA for values over 7 but I guess it was becasue of

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

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

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!