Common Date/Time formulas for Sharepoint – Calculated Fields using Calculated column

Calculate Date -  depends on the week and weekends:

=IF(WEEKDAY(Created)<4,Created+3,IF(WEEKDAY(Created)=4,Created+5,Created+4))

Example :-

If suppose you have submitted the form on 22nd May(Tuesday) then task completion date will be 25th May (Friday).
If  form is submitted on 24th May (Thursday) then the task completion date will be 28th May(Monday).
Same as if form submitted on 25th May (Friday) then the task completion date will be 29th May(Tuesday).

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”

You can get Get more formulas from :-

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

OR

http://msdn.microsoft.com/en-us/library/bb862071.aspx

Check out this one..!!
Any Questions/Sugesstions ....!!