0
=IF(ISBLANK[Version-created];"";[Version-created]+365)
=IF([Version-created]="";"";[Version-created]+365)

I created these formulas to first detect if there is a date in the cell, if there is, to add 365 days and be displayed in a new column "valid-until" and be a NULL if there isn't. [Version-created] is a date and time value.

Originally I wrote them in Excel, since the workspace in SharePoint is impractical. It always shows an error message but IF and ISBLANK both are formulas that can be used in SP. Both options work in Excel but not in SP. The error message unfortunately doesn't help, it says the formula contains a syntax error or is not supported.

This then should be followed by a column "Status" with the

=IF([valid-until]= ""; "in-progress"; [valid-until]>(TODAY());"approved";[valid-until]<=(TODAY());"expired") 

which also works in Excel but won't work on SP because of the TODAY. Is there an easy workaround?

Thanks in advance if you can help me with one or both problems.

3
  • What is the error message? Also, is [Version-created] column in your list with date and time type? Feb 14 at 12:19
  • It shows that either formula contains a syntax error or is not supported. And [Version-created] is a date and time type.
    – LisaMayr
    Feb 14 at 12:25
  • check my answer given below, it should work for you. Feb 14 at 12:31

1 Answer 1

0

Try using this formula for your calculated column:

=IF(ISBLANK([Version-created]),"",TEXT([Version-created]+365,"mm/dd/yyyy"))

Output:

enter image description here

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(,). For example: If you are using German language for your SharePoint site
  2. Use correct display names of your columns in the formula

Official Documentations:

  1. Calculated Field Formulas
3
  • I tried your code (swapped the commas with semicolons, I use the German version) and it still shows the same error message. I also used the "add to formular" for the column names, these should be correct?
    – LisaMayr
    Feb 14 at 12:44
  • can you show the new formula you are using? Add it here in comments. Feb 14 at 12:52
  • =WENN(ISTLEER([Version-erstellt-am]);"";TEXT([Version-erstellt-am]+365;"mm/dd/yyyy"))
    – LisaMayr
    Feb 14 at 12:59

Your Answer

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

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