Date and Time Information for the Calculated Column

I’ve hadTaming the Elusive ‘Calculated Column’” bookmarked forever. All in one place were the links I needed for figuring out the formulas and functions for calculated columns. Unfortunately the Microsoft pages those links point to have been “improved” and I don’t think they’re as helpful as they used to be. Examples of common formulas doesn’t list everything. I’ve found a list of all the functions, but it’s not grouped in any helpful way (as far as I can tell.)

Examples of Date and Time Formulas

I have my own examples that aren’t specified in the Microsoft Examples (they’re implied but never explicitly shown.)

To extract the date and time in different ways:

Column1 Formula Description (result)
9/5/2012 12:03 PM =TEXT(Column1,”YYYYmmDDHHmm”) Show date in years, months, days, hours and minutes (201209051203)
9/5/2012 12:03 PM =TEXT(Column1,”YYYY”) Show the 4 digit year (2012)
9/5/2012 12:03 PM =TEXT(Column1,”MM”)&”-“&TEXT(Column1,”MMMM”) Show the numerical value of the month, in two digits, and the actual month name (09-September) I use this for sorting the months correctly.

Other Date Functions:

DATE function Returns the sequential serial number that represents a particular date.
DATEDIF function Calculates the number of days, months, or years between two dates.
DATEVALUE function Returns the serial number of the date represented by date_text.
DAY function Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
DAYS360 function Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations.
EXPONDIST function Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash.
HOUR function Returns the hour of a time value.
MINUTE function Returns the minutes of a time value.
MONTH function Returns the month of a date represented by a serial number.
SECOND function Returns the seconds of a time value.
TIME function Returns the decimal number for a particular time.
TIMEVALUE function Returns the decimal number of the time represented by a text string.
TODAY function Returns the serial number of the current date.
WEEKDAY function Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
YEAR function Returns the year corresponding to a date.

Date and Time Information for the Calculated Column

Text Formulas for the Calculated Column

Conditional Formulas for the Calculated Column

Mathematical Information for the Calculated Column

Advertisements

5 Responses to Date and Time Information for the Calculated Column

  1. Pingback: Text Formulas for the Calculated Column « SharePoint Diva

  2. Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva

  3. Pingback: Mathematical Information for the Calculated Column « SharePoint Diva

  4. westerdaled says:

    Useful .. I want to create a created field via Powershell based on =TEXT(Column1,”mmYYYY”). Just wondered if you have ever done anything like that.

  5. sharepointdiva says:

    I have not, but this may be what you’re looking for: http://sharepoint.stackexchange.com/questions/93075/creating-a-calculated-column-with-replace-in-powershell

    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

    $site = Get-SPweb “http://test.com”

    $mList=$site.Lists[“TestCal”]

    $mList.Fields.Add(“Count”, “Calculated”, 0)
    $SPField = $mList.Fields.GetField(“Count”)
    $SPField.OutputType=”Number”
    $SPField.Formula=’=LEFT(REPLACE(Title,SEARCH(“.”,Title),1,””),SEARCH(“.”,REPLACE(Title,SEARCH(“.”,Title),1,””))-1)’
    $SPField.update()

    $site.Dispose()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: