How to check for a date/nulls and compare to the current date?

Question

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

Answer 1

Try using this formula for your calculated column:

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

Output:

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