Need help with calculated column formula in SP 2010
I am using a calculated column to show how many days are left before we receive a product. I am using the following formula for that:
It shows correctly how many days are left but it shows an error when it is past due (#NUM! - I guess because of the negative value?). My regular expressions knowledge ends here. I would like to change this formula to say "Past Due" instead of an error. How should it look like? Thanks!
IF([Product Expected],(DATEDIF(Today,[Product Expected],"d")),"NA",(DATEDIF(Today,[Product Expected],"d"))It shows correctly how many days are left but it shows an error when it is past due (#NUM! - I guess because of the negative value?). My regular expressions knowledge ends here. I would like to change this formula to say "Past Due" instead of an error. How should it look like? Thanks!
ASKER
It works but we are missing one condition. It needs to show "NA" if the Product Expected field is empty. Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works great but after brainstorming I need to complicate it a little bit more. I would like to merge it with a different formula that takes into consideration other dates and also change the number of days into text ranges. 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)
Let me know if you can figure it out too. It is way above my abilities now. I wanted to increase the point available but couldn't find it. We can move it into a separate question if you want. Thanks!
- 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)
Let me know if you can figure it out too. It is way above my abilities now. I wanted to increase the point available but couldn't find it. We can move it into a separate question if you want. Thanks!
Considering the earlier solution worked, and its a new request i would suggest to move it to a new question. In the meantime, i will work on a solution for you!
ASKER
Another question posted and I am closing this one. Thanks!!!
Does this work?