SharePoint Calculated Fields Reference?

I’ve looked for quite a while now for a good reference for what formulas are available to me when I create a calculated column in SharePoint.  Today I think I stumbled on the easy answer: Excel.

Here’s a simple example. I wanted to return the year for the Created date.  When I just used:

=YEAR([Created])

I got "2,007", but I wanted just "2007".  I wasn’t sure of the syntax, so I went into Excel and created the formula:

=TEXT(YEAR(TODAY()),"####")

which gave me what I wanted.  (I know, this is so simple that I should have just known.)  I took that formula and pasted into my Site Column calculation, replacing TODAY() with [Created], ending up with:

=TEXT(YEAR([Created]),"####")

Exactly what I wanted.

Now, I expect that there are some differences between Excel formulas and SharePoint formulas, but in most cases, I think that this will be a reliable way to build them up, plus Excel gives you nice contextual help when you can’t remember things like "####" means 4 clean digits!

5 Comments

  1. Magnus:
     
    Glad you figured it out!  I’m guessing that the reason you needed to use a ‘;’ where I used a ‘,’ is due to a different language setting.  I’m working in United States English (1033), so all of my posts will reflect that.
     
    M.
    Reply
  2. =TEXT(YEAR([Created]),"####") does not work in my environment.

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

    Any clues?

    Reply
  3. I haven’t done any experimentation in the differences.  I’ve really just used Excel in this case to validate my formulas.
    Reply
  4. Have ou discovered any differences between Excel formulae and SharePoint formulae?  According to Excel help, you can nest up to 64 IF’s in a statement.  I’m trying to nest 18 and SharePoint errors when the formula is entered in the calculated field.  However, the same formula works fine in Excel.  Ever seen this before?
    Reply

Have a thought or opinion?