Common Date Time formulas for Sharepoint

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])

If you want your week to start from Monday to Sunday, include an extra parameter value to your WEEKDAY() function

First day of the week for a given date:

=[Start Date]-WEEKDAY([Start Date],2)+1

Last day of the week for a given date:

=[End Date]+7-WEEKDAY([End Date],2)
First day of the month for a given date:

=DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))

TODAY + 1 hour formula
You can use formula like (1/24th of a day or 1 hour to the rest of us is 0.04167!)


=[Created]+0.04167

But we can’t use the Created/Modified field in the formula for a new record because it doesn’t exist yet.


OK, so what if we try and use this in the Default Value column as
=Today+0.04167

This will always be ‘Today at 1AM’ rather than ‘Today in exactly 1 hour’ as Today uses 12:00 AM as the time offset. Unfortunately there is no [Now] function in SharePoint.

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”
You can get Get more formulas from

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

RunWithElevatedPrivileges is not working - invalid/wrong security context

To fix this you need to get a news ite reference

SPSecurity.RunWithElevatedPrivileges(delegate()

{

//Gets a new security context using SHAREPOINT\system
//or by using (SPSite oSite = new SPSite(this.Page.Request.Url.ToString()))

using(SPSite oSite = new SPSite(SPContext.Current.Site.ID))
{
   using (SPWeb oWeb = oSite.OpenWeb())
   {
    oWeb.AllowUnsafeUpdates = true;
    // Add a record to the new list
    SPList oList = oWeb.Lists["myListName"];
    if (oList != null)
   {
    SPListItem item = oList.Items.Add();
    item["Title"] = "new item";

    // item.update to committ
    item.Update();
    }
 }
}
});

How to integrate Google Map

To integrate Google Map with MOSS is very easy, you only need to add to content editor web part with some piece of HTML. For more information see following url

http://vspug.com/bobbyhabib/2007/12/05/create-a-google-map-web-part-for-free/

How to modify the values of "Created By", "Modified By" columns in SharePoint lists

SPSite oSite = new SPSite("http://portal");
SPWeb oWeb = oSite.OpenWeb();
SPList oList = oWeb.Lists["mylist"];
SPListItemCollection oListCollection = oList.Items;
foreach (SPListItem oListItem in oListCollection)
{
SPFieldUserValue oUser = new SPFieldUserValue(oWeb, oWeb.CurrentUser.ID, oWeb.CurrentUser.LoginName);

// or you can hard code the value like this,

SPFieldUserValue oUser = new SPFieldUserValue(oWeb, 14, "Ashish Kanoongo");
oListItem["Author"] = oUser;//created by - "14;#Ashish Kanoongo"
oListItem["Editor"] = oUser;//modified by - "14;#Ashish Kanoongo"
oListItem.Update();
}
oWeb.Update();

How to create Custom Aspx Page in Sharepoint

For creating custom aspx page in Sharepoint follow the steps

1. First, create a Class Library project in vs 2005/2008.
2. Add Microsoft.SharePoint dll reference
3. Create a file called HelloWorld.aspx or or you can create a sepeate code behind .cs file but add code into you
4. Sign a strong key and build the project.
5. Add the assembly into GAC
6. Create a folder for example say 'test' and then Copy the HelloWorld.aspx to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Test
7. Now run the page as http://portal/_layouts/Test/HelloWorld.aspx

<%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%>
<%@ Page Language="C#" MasterPageFile="~/_layouts/application.master"
         Inherits="Microsoft.SharePoint.WebControls.LayoutsPageBase"  %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<script runat="server">
  protected override void OnLoad(EventArgs e) {
    //SPWeb site = SPContext.Current.Web;
    SPWeb site = this.Web;
    lblSiteTitle.Text = site.Title;
    lblSiteID.Text = site.ID.ToString().ToUpper();
  }
</script>

<asp:Content ID="Main" contentplaceholderid="PlaceHolderMain" runat="server">
  <table border="1" cellpadding="4" cellspacing="0" style="font-size:12">
    <tr>
      <td>Site Title:</td>
      <td><asp:Label ID="lblSiteTitle" runat="server" /></td>
    </tr>
    <tr>
      <td>Site ID:</td>
      <td><asp:Label ID="lblSiteID" runat="server" /></td>
    </tr>
  </table>
</asp:Content>

<asp:Content ID="PageTitle" contentplaceholderid="PlaceHolderPageTitle" runat="server">
 Hello World
</asp:Content>

<asp:Content ID="PageTitleInTitleArea" runat="server"
             contentplaceholderid="PlaceHolderPageTitleInTitleArea" >
 Page Title 'Hello World' of Application Page
</asp:Content>