Skip to main content

Common Date Time formulas for Sharepoint – Calculated Fields


Get Week of the year
=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1
First day of the week for a given date:
=[Start Date]-WEEKDAY([Start Date])+1
Last day of the week for a given date:
=[End Date]+7-WEEKDAY([End Date])
First day of the month for a given date:
=DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))
Last day of the month for a given year (does not handle Feb 29). Result is in date format:
=DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End Date]))
Day Name of the week : e.g Monday, Mon
=TEXT(WEEKDAY([Start Date]), “dddd”)
=TEXT(WEEKDAY([Start Date]), “ddd”)

The name of the month for a given date – numbered for sorting – e.g. 01. January:
=CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” , “08. August” , “09. September” , “10. October” , “11. November” , “12. December”)
Get Hours difference between two Date-Time :
=IF(NOT(ISBLANK([End Time])),([End Time]-[Start Time])*24,0)
Date Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”
 
 
8 Votes

Comments

Post a Comment

Popular posts from this blog

SharePoint Game Webpart

http://spgameoflifewebpart.codeplex.com/ Goal: The Game of Life web part was created for a talk I presented at SharePoint Saturday New York in 2009. At the time, SharePoint 2010 was still in Beta and most people hadn't see the Enterprise Metadata Management (taxonomy) features. I used the Game of Life web part as an introduction to the new taxonomy API. I offered to provide the code, so here it is. Requirements ----------------------- - SharePoint Server 2010 with Enterprise Features - Visual Studio 2010 (inc. SharePoint tools) Setup ----------------------- 1. Add your account as a Term Store Administrator. This right is independent to Site Collection Administrator or Farm Administrator status. To open the Term Store Management Tool go to Site Actions > Site Settings > Site Administartion > Term Store Management. You can also  open the same interface from Central Administration > Manage Service Applications > Managed Metadata Service. 2. Run the sol...
Post a Comment
Read more

CAML Query Helper Class

http://sharepointcamlhelper.codeplex.com/ https://camldotnet.svn.codeplex.com/svn/JohnHolliday.Caml.Net/CAML.cs CAMLManager mgr = new CAMLManager(); mgr.QueryGroups.Add(new QueryGroup("Title", Types.FieldTypes.Text, Types.QueryTypes.Eq, "A Title")); mgr.QueryGroups.Add(new QueryGroup(Types.JoinTypes.Or, "Title", Types.FieldTypes.Text, Types.QueryTypes.Eq, "Another Title")); mgr.QueryGroups.Add(new QueryGroup(Types.JoinTypes.And, QueryGroup.MergeTypes.Contain, "Total", Types.FieldTypes.Number, Types.QueryTypes.Geq, "2")); string caml = mgr.GetCAML(); Will provide the following: <Where>   <And>     <Or>       <Eq>         <FieldRef Name="Title" /><Value Type="Text">A Title</Value>       </Eq>       <Eq>         <FieldRef Name="Title" /><Value Type="Text">Another Title</Value>     ...
1 comment
Read more