Date and Time Information for the Calculated Column
September 6, 2012 6 Comments
I’ve had “Taming 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
SharePoint Diva 
Pingback: Text Formulas for the Calculated Column « SharePoint Diva
Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva
Pingback: Mathematical Information for the Calculated Column « SharePoint Diva
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.
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()
=TEXT(NOW(),”hh:mm”)