Sharepoint Common Date Formulas For Calculated Columns

By peter.stilgoe









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) DAY(Today) <=MONTH(Created),DAY(Today) >=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today) “&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)

More From pstilgoe

Blog Margeting Related Posts Plugin For pstilgoeAsk pstilgoe To Recommend Your Posts Blog Marketing Related Posts Plugin Counter

Share

,

About... peter.stilgoe

peter.stilgoeThis author published 482 posts in this site.
Sharepoint, InfoPath, K2, Nintex, Business Process Mapping, Business Intelligence, Automation, ECM, Document Management, Document Imaging, Internet Marketing & Online Business Consultant Email / MSN: pstilgoe@hotmail.com LinkedIn: Pete Stilgoe - Sharepoint Consultant









Share

FacebookTwitterEmailWindows LiveTechnoratiDeliciousDiggStumbleponMyspaceLikedin

2 Comments

  • At 2011.08.16 20:42, e said:

    formula convert month names (text) to month numbers:

    =IF(Date_Month=”January”,”01″,IF(Date_Month=”February”,”02″,IF(Date_Month=”March”,”03″,IF(Date_Month=”April”,”04″,IF(Date_Month=”May”,”05″,IF(Date_Month=”June”,”06″,IF(Date_Month=”July”,”07″,”")))))))&IF(Date_Month=”August”,”08″,IF(Date_Month=”September”,”09″,IF(Date_Month=”October”,”10″,IF(Date_Month=”November”,”11″,IF(Date_Month=”December”,”12″,”")))))

    [Reply]

    [Reply]
    • At 2012.01.05 00:41, Dale Sprague said:

      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

      This doesn’t work. For 1/4/2012, I get 40910.5 for the Week Number completed. My previous calculation doesn’t work either because I’m getting week number 5 for an item completed on 1/4/2012.

      Any thoughts?

      [Reply]

      [Reply]
      (Required)
      (Required, will not be published)