Calculated Fields: Examples of Common Formulas

http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

Tip: If your column name has a space character in it, you have to put it in brackets when creating a calculated column. For example: the "Assigned To" field, when you want to use it in a formula is written as "=[Assigned To]". If there are no spaces in the name of the field, simply put the name of the field into the formula, as such: "=TEXT([Created],"dddd mmm-dd-yyyy")".

In addition, here are some more helpful calculations:

Nice Date for Humans:

[Calculated field based on a field called "Class Start", a date/time field.]

=TEXT([Class Start],"dddd mmm-dd-yyyy hh:mm")

Gives us: "Thursday, Jan-15-2009 09:31"

Leading Zeros in Month Number:

=YEAR(Created)&" "&IF(MONTH(Created)<10,"0"&month(created),month(created))

Gives us: "2009 01"

This is VERY useful for sorting items by date. Add the day and you are set to go:

=YEAR(Created)&"-"&IF(MONTH(Created)<10,"0"&month(created),month(created))

Last Day of Last Month:

=DATE(YEAR(DATE(YEAR(Created),MONTH(Created)-1,DAY(Created))),MONTH(DATE(YEAR(Created),MONTH(Created)-1,DAY(Created)))+1,0)

Useful for reporting - if you have a document library where you upload a bunch of reports from last quarter or last month, this will give you a jumping off point to sort and group by the report's 'end date'.

 

I will add more calculations as I come across them.