Need help with calculated column formula in SP 2010

MisUszatek
MisUszatek used Ask the Experts™
on
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:

IF([Product Expected],(DATEDIF(Today,[Product Expected],"d")),"NA",(DATEDIF(Today,[Product Expected],"d"))

Open in new window


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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
=IF(([Product Expected]-Today)>0, ([Product Expected]-Today), "Past Due")

Does this work?

Author

Commented:
It works but we are missing one condition. It needs to show "NA" if the Product Expected field is empty. Thanks!
Top Expert 2011
Commented:
try using this...

=IF(ISBLANK([Product Expected]), "NA",IF(([Product Expected]-Today)>0, ([Product Expected]-Today), "Past Due"))
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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!
Top Expert 2011

Commented:
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!

Author

Commented:
Another question posted and I am closing this one. Thanks!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial