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!

Microsoft SharePointProgramming

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.

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

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?

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

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

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

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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!