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
 

Move your Site Collection from One Content Database to another Content Database

Hi Friends,

It’s a question that comes up in every deployment of SharePoint: Should I have many site collections or one site collection with many sub sites?  My definitive answer: “It depends”.  

Under some circumstances, you might want to move one or more site collections to a different content database. For example, a site collection can outgrow the content database on which it resides on, and you would have to move the site collection to a larger content database. On the flip side, if site collections are too small in size you may want to combine several site collections onto one content database. 

Prerequisites:

User Account

• The user account running the below process needs access to s server running Windows PowerShell 2.0 and a member of the following groups
• The db_owner fixed database role and the SharePoint_Shell_Access role in the SQL Server source content database, administration content database, destination content database, and configuration database.
• The WSS_ADMIN_WPG group on the local computer.

For this process to work, the following must be true

• The destination content database must already exist.
• The source content database and destination content database must be located on the same instance of SQL Server.
• The source content database and destination content database must be attached to the same Web application.

The Steps

• Add a content databae to a web application by using Windows PowerShell – This is obviously optional if you may want to copy the site collection to an existing content database
• Move your site collection to another content database

To add a content database to a web application by using windows PowerShell

• Start SharePoint 2010 Management Shell
• Type in the following command

New-SPContentDatabase -Name <ContentDbName> -WebApplication <WebApplicationName>

• Replace <ContentDbName> with the name of the content database to create
• Replace<WebApplicationName> with the name of the Web application to which the new database is attached.

To move your site collection to another database:

• At the Windows PowerShell command prompt, type the following command:

Move-SPSite <http://ServerName/Sites/SiteName> -DestinationDatabase <DestinationContentDb>

• Replace<http://ServerName/Sites/SiteName> with the name of the site collection.
• Replace <DestinationContentDb> with the name of the destination content database.

To move multiple site collections fron one content database into another:

• At the Windows PowerShell command prompt, type the following command:

Get-SPSite -ContentDatabase <SourceContentDb> | Move-SPSite -DestinationDatabase <DestinationContentDb>

• Replace <SourceContentDb> with the name of the original content database.
• Replace <DestinationContentDb> with the name of the destination content database.