ISBLANK WITH OTHER CRITERIA

Question

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.

Answer 1

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

Answer 2

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