1

I have a sharepoint calculated field that looks at a date and values in two other fields to populate with either a blank or a date.

First criteria: Units > 0

Second criteria: Type = "RDS"

Third criteria: Estimated Ship Date - if that date is blank, display a blank - can't be a zero because I send the data in that field to a SQL data table and if it's a zero the upload fails.

If the Estimated Ship Date is populated, the units are greater than 0 and the type is equal to "RDS" then it takes the Estimated Ship Date minus 28 days.

When I use this formula, I keep getting a zero when the Estimated ship date is blank.

=IF([Units]>0,IF([Type]="RDS",IF([Estimated Ship Date]<>"",[Estimated Ship Date]-28,"")))

I've tried moving the Estimated Ship Date to the beginning of the formula and using ISBLANK but it keeps going to "Technical Issue" which is totally useless.

2 Answers 2

0

Use calculated column formula like this:

=IF(ISBLANK([Estimated Ship Date]),"",IF(AND([Units]>0,[Type]="RDS"),[Estimated Ship Date]-28,""))

Note:

  1. Sometimes comma(,) does not work in formula (It is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Make sure you are using correct display name of columns in your formula (case sensitive).
0

You need to use the internal name of the column in you formula.

How to fetch the internal name of a field from UI:

Go to List settings >> Columns >> Click the column Do you use any fuel for non-travel purposes? >> See the internal name at the end of the URL(after ...Field=).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.