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

Popular posts from this blog

SharePoint Farm Installation

Windows Cluster : http://technet.microsoft.com/en-us/library/bb727114.aspx http://blogs.msdn.com/b/clustering/archive/2008/01/18/7151154.aspx SQL Cluster : http://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%E2%80%93-part-3/ Network Load Balancing http://www.jppinto.com/2009/05/install-and-configure-wlbs-nlb-on-windows-server-2008/ SharePoint 2010 Farm Installation http://www.khamis.net/blog/Lists/Posts/Post.aspx?ID=48

How to store the sharepoint List or List Item within the recycle bin of the site while deleting it programmatically ?

Whenever we delete a sharepoint list or list item from the User Interface it get stored within the recycle bin of the site. But when we delete it programmatically it get's deleted permanently. So what we can do to store the List or List Item within the recycle bin while deleting it programmatically ? Generally we use the list.Delete() or item.Delete() method to delete the list or item. Instead of using the Delete() method we can use the Recycle() method i.e list.Recycle() or item.Recycle(). This will store the item within the recycle bin.