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? Commented Feb 14, 2023 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
    Commented Feb 14, 2023 at 12:25
  • check my answer given below, it should work for you. Commented Feb 14, 2023 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
    Commented Feb 14, 2023 at 12:44
  • can you show the new formula you are using? Add it here in comments. Commented Feb 14, 2023 at 12:52
  • =WENN(ISTLEER([Version-erstellt-am]);"";TEXT([Version-erstellt-am]+365;"mm/dd/yyyy"))
    – LisaMayr
    Commented Feb 14, 2023 at 12:59

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.