1

Good Afternoon,

Goal: Calculated column called "Certificate Status" (Data type returned: single-line of text) that identifies if an individual has provided the certificate for a required training.

If the column "Certificate Date" is blank, the formula returns "Missing." If there is a date in "Certificate Date" and the difference between the "Certificate Date" and "Today" is greater than 365, then the formula returns "Expired", if that is not the case, the formula returns "Good".

I keep getting the error:

"The formula contains a syntax error or is not supported."

Columns feeding into calculated column "Certificate Status":

  1. Certificate Date = mm/dd/yyyy
  2. Today = mm/dd/yyyy (Defaults to "Today")
  3. DATEDIF(Certificate Date) = DATEDIF([Certificate Date],Today*,"D") ---(Data type returned: Number)

*After saving DATEDIF(Certificate Date) and going back in to inspect the formula, SharePoint removes the brackets around "Today".

Any support in identifying the discrepancy and/or clearing up that SharePoint calculated columns cannot support what I'm trying below is greatly appreciated.

"Certificate Status" formula is as follows:

=IF(ISBLANK[Certificate Date],
      "Missing",
   IF([DATEDIF(Certificate Date)]>365,
      "Expired",
      "Good"
   )
)
  • See the "Notes" block at the bottom of the Functions Overview section on this page, while you can use TODAY in a formula for the Default value of a column, you cannot use TODAY in a Calculated Column. – willman Feb 22 at 20:37
1

Resolved.

Fixes:

  1. ISBLANK function requires parentheses around the value it's affecting: ([Certificate Date]) and

  2. [DATEDIF(Certificate Date)]>365, needs to have parenthesis around it as well.

Final formula:

=IF(ISBLANK([Certificate Date]),"Missing",IF(([DATEDIF(Certificate Date)]>365),"Expired","Good"))
| improve this answer | |
  • I think you have pasted this incorrectly in your answer. DATEDIF(Certificate Date) would cause this formula to fail, it is missing two parameters. – willman Feb 22 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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