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