Search code examples
sharepoint-onlinecalculated-columns

Sharepoint Calculated Column based on creation before or after 15th day of month

I have worked though this in a few different ways and had little luck identifying where i'm being a bone head. Here is what I have.

SharePoint List with column Date_received data type is date

Same list has column named storage Data type is Currency

If the item is received on or before day 15 of the month the value of storage should be 7 if received after the 15th day the value should be 3.50.

=if(DAY([Date_Received]<=15,3.50,7.00))

This is one of many variation of the formula I have used All provide a syntax or not supported error after clicking OK.

Solution

  • You need to close the round bracket for DAY() earlier. Not at the end of the IF, but after the name of the column from which you want to extract the day, i.e. the only parameter of the DAY() function.

    =if(DAY([Date_Received])<=15,3.50,7.00)

    As a general tip: with simple functions like these, you can build and troubleshoot them in Excel. Build a table with the same column names as you need, then you can even use the column names of the structured referencing. Just remove the @ sign and the extra pair of brackets.

    enter image description here

    Excel has syntax highlighting and you can step through a formula with the Evaluate Formula tool to find out where things go wrong.