Generate thumbnail for list attachment

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Linq;
 
namespace QuarterlyAward
{
    public partial class Thumbnail : LayoutsPageBase
    {
        /// <summary>
        /// Create thumbnail for list attachment
        /// Query string parameters:
        /// listID: List id
        /// itemID: Item id of list
        /// w: Specified thumbnail width
        /// h: Specified thumbnail height
        /// c: Use cache if cache exist and parameter value is not set
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            string cacheID = string.Format("{0}#{1}#{2}#{3}",
                Request.QueryString["listID"],
                Request.QueryString["itemID"],
                Request.QueryString["w"],
                Request.QueryString["h"]);
            byte[] thumbnailBinary = null;
 
            // Cache detection
            if (string.IsNullOrEmpty(Request.QueryString["c"]) && Cache[cacheID] != null)
            {
                thumbnailBinary = Cache[cacheID] as byte[];
                // Output cached thumbnail
                Response.Clear();
                Response.OutputStream.Write(thumbnailBinary, 0, thumbnailBinary.Length);
                Response.ContentType = "image/png";
                Response.End();
                return;
            }
 
            // Lack of parameter
            if (string.IsNullOrEmpty(Request.QueryString["listID"]) || string.IsNullOrEmpty(Request.QueryString["itemID"]))
            {
                Response.StatusCode = 404;
                return;
            }
 
            // Validate parameter
            int itemID = -1;
            if (!int.TryParse(Request.QueryString["itemID"], out itemID))
            {
                Response.StatusCode = 404;
                return;
            }
 
            int width = -1, height = -1;
            int.TryParse(Request.QueryString["w"], out width);
            int.TryParse(Request.QueryString["h"], out height);
            if (width == -1 && height == -1)
            {
                Response.StatusCode = 404;
                return;
            }
 
            #region Get attachment and generate thumbnail
            using (var web = SPContext.Current.Site.OpenWeb())
            {
                var list = web.Lists[new Guid(Request.QueryString["listID"])];
                var item = list.GetItemById(itemID);
 
                if (item == null || item.Attachments == null || item.Attachments.Count == 0)
                {
                    Response.StatusCode = 404;
                    return;
                }
 
                var imageType = new string[] { ".jpg", ".jpeg", ".gif", ".png", ".bmp" };
                // Get attachment folder
                var folder = item.ParentList.RootFolder.SubFolders["Attachments"].SubFolders[item.ID.ToString()];
                // Get image attachment
                var files = folder.Files
                                  .OfType<SPFile>()
                                  .Where(f => imageType.Contains(System.IO.Path.GetExtension(f.ServerRelativeUrl.ToLower())));
                if (!files.Any())
                {
                    Response.StatusCode = 404;
                    return;
                }
                var file = files.First();
 
                // Open image binary in memory
                var ms = new System.IO.MemoryStream(file.OpenBinary());
                // Load image instance
                var source = System.Drawing.Image.FromStream(ms);
                // Limit width & height
                if (source.Width < width || width < 1) width = source.Width;
                if (source.Height < height || height < 1) height = source.Height;
                var size = adaptProportionalSize(new System.Drawing.Size(width, height), source.Size);
 
                // Create thumbnail
                var thumbnailImage = source.GetThumbnailImage(size.Width, size.Height, null, IntPtr.Zero);
                // Get thumbnail binary
                ms = new System.IO.MemoryStream();
                thumbnailImage.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                thumbnailBinary = ms.ToArray();
 
                // Cache 30 min
                Cache.Add(
                    cacheID,
                    thumbnailBinary,
                    null,
                    System.Web.Caching.Cache.NoAbsoluteExpiration,
                    new TimeSpan(0, 30, 0),
                    System.Web.Caching.CacheItemPriority.High,
                    null);
            }
            #endregion
 
            // Output thumbnail
            Response.Clear();
            Response.OutputStream.Write(thumbnailBinary, 0, thumbnailBinary.Length);
            Response.ContentType = "image/png";
            Response.End();
        }
 
        /// <summary>
        /// Caculate appropriate size for thumbnail
        /// </summary>
        /// <param name="szMax"></param>
        /// <param name="szReal"></param>
        /// <returns></returns>
        System.Drawing.Size adaptProportionalSize(System.Drawing.Size szMax, System.Drawing.Size szReal)
        {
            int nWidth;
            int nHeight;
            double sMaxRatio;
            double sRealRatio;
 
            if (szMax.Width < 1 || szMax.Height < 1 || szReal.Width < 1 || szReal.Height < 1)
                return System.Drawing.Size.Empty;
 
            sMaxRatio = (double)szMax.Width / (double)szMax.Height;
            sRealRatio = (double)szReal.Width / (double)szReal.Height;
 
            if (sMaxRatio < sRealRatio)
            {
                nWidth = Math.Min(szMax.Width, szReal.Width);
                nHeight = (int)Math.Round(nWidth / sRealRatio);
            }
            else
            {
                nHeight = Math.Min(szMax.Height, szReal.Height);
                nWidth = (int)Math.Round(nHeight * sRealRatio);
            }
 
            return new System.Drawing.Size(nWidth, nHeight);
        }
    }
}

Complete Lifecycle of an ASP.Net page and controls

Page Master Page Aspx.page User Control Custom Web Control

Complete Trace

	Category	Message
1	Page	ResolveAdapter()
2	Page	DeterminePostBackMode()
3	aspx.page	Begin PreInit
4	Page	Start OnPreInit(EventArgs e)
5	Page	End OnPreInit(EventArgs e)
6	MasterPage	Start Construct()
7	MasterPage	End Construct()
8	Page	HasControls()
9	Page	Start RemovedControl(Control control)
10	Page	End RemovedControl(Control control)
11	MasterPage	Start FrameworkInitialize()
12	MasterPage	End FrameworkInitialize()
13	MasterPage	Start AddParsedSubObject(object obj)
14	MasterPage	CreateControlCollection()
15	MasterPage	Start AddedControl(Control control, int index)
16	MasterPage	End AddedControl(Control control, int index)
17	MasterPage	End AddParsedSubObject(object obj)
18	MasterPage	Start AddParsedSubObject(object obj)
19	MasterPage	Start AddedControl(Control control, int index)
20	Page	ResolveAdapter()
21	MasterPage	End AddedControl(Control control, int index)
22	MasterPage	End AddParsedSubObject(object obj)
23	MasterPage	Start AddParsedSubObject(object obj)
24	MasterPage	Start AddedControl(Control control, int index)
25	MasterPage	End AddedControl(Control control, int index)
26	MasterPage	End AddParsedSubObject(object obj)
27	UserControl	Start Construct()
28	UserControl	End Construct()
29	UserControl	Start FrameworkInitialize()
30	UserControl	End FrameworkInitialize()
31	CustomWebControl	Constructor Test()
32	CustomWebControl	Start ApplyStyleSheetSkin(Page page)
33	CustomWebControl	End ApplyStyleSheetSkin(Page page)
34	MasterPage	Start AddParsedSubObject(object obj)
35	MasterPage	Start AddedControl(Control control, int index)
36	MasterPage	End AddedControl(Control control, int index)
37	MasterPage	End AddParsedSubObject(object obj)
38	MasterPage	Start AddParsedSubObject(object obj)
39	MasterPage	Start AddedControl(Control control, int index)
40	MasterPage	End AddedControl(Control control, int index)
41	MasterPage	End AddParsedSubObject(object obj)
42	Page	Start AddedControl(Control control, int index)
43	Page	End AddedControl(Control control, int index)
44	aspx.page	End PreInit
45	aspx.page	Begin Init
46	Page	ResolveAdapter()
47	MasterPage	ResolveAdapter()
48	UserControl	ResolveAdapter()
49	UserControl	Start OnInit(EventArgs e)
50	UserControl	End OnInit(EventArgs e)
51	UserControl	Start TrackViewState()
52	UserControl	End TrackViewState()
53	CustomWebControl	ResolveAdapter()
54	CustomWebControl	Start OnInit(EventArgs e)
55	CustomWebControl	End OnInit(EventArgs e)
56	CustomWebControl	Start TrackViewState()
57	CustomWebControl	End TrackViewState()
58	MasterPage	Start OnInit(EventArgs e)
59	MasterPage	End OnInit(EventArgs e)
60	MasterPage	Start TrackViewState()
61	MasterPage	End TrackViewState()
62	Page	Start OnInit(EventArgs e)
63	Page	End OnInit(EventArgs e)
64	Page	Start TrackViewState()
65	Page	End TrackViewState()
66	aspx.page	End Init
67	aspx.page	Begin InitComplete
68	Page	Start OnInitComplete(EventArgs e)
69	Page	End OnInitComplete(EventArgs e)
70	aspx.page	End InitComplete
71	aspx.page	Begin PreLoad
72	Page	Start OnPreLoad(EventArgs e)
73	Page	End OnPreLoad(EventArgs e)
74	aspx.page	End PreLoad
75	aspx.page	Begin Load
76	Page	Start OnLoad(EventArgs e)
77	Page	Page_Load(object sender, EventArgs e)
78	Page	End OnLoad(EventArgs e)
79	MasterPage	Start OnLoad(EventArgs e)
80	MasterPage	Page_Load(object sender, EventArgs e)
81	MasterPage	End OnLoad(EventArgs e)
82	UserControl	Start OnLoad(EventArgs e)
83	UserControl	Page_Load(object sender, EventArgs e)
84	UserControl	End OnLoad(EventArgs e)
85	CustomWebControl	Start OnLoad(EventArgs e)
86	CustomWebControl	End OnLoad(EventArgs e)
87	aspx.page	End Load
88	aspx.page	Begin LoadComplete
89	Page	Start OnLoadComplete(EventArgs e)
90	Page	End OnLoadComplete(EventArgs e)
91	aspx.page	End LoadComplete
92	aspx.page	Begin PreRender
93	Page	Start EnsureChildControls()
94	Page	ResolveAdapter()
95	Page	Start CreateChildControls()
96	Page	End CreateChildControls()
97	Page	End EnsureChildControls()
98	Page	Start OnPreRender(EventArgs e)
99	Page	End OnPreRender(EventArgs e)
100	MasterPage	Start EnsureChildControls()
101	MasterPage	ResolveAdapter()
102	MasterPage	Start CreateChildControls()
103	MasterPage	End CreateChildControls()
104	MasterPage	End EnsureChildControls()
105	MasterPage	Start OnPreRender(EventArgs e)
106	MasterPage	End OnPreRender(EventArgs e)
107	UserControl	Start EnsureChildControls()
108	UserControl	ResolveAdapter()
109	UserControl	Start CreateChildControls()
110	UserControl	End CreateChildControls()
111	UserControl	End EnsureChildControls()
112	UserControl	Start OnPreRender(EventArgs e)
113	UserControl	End OnPreRender(EventArgs e)
114	CustomWebControl	Start EnsureChildControls()
115	CustomWebControl	ResolveAdapter()
116	CustomWebControl	Start CreateChildControls()
117	CustomWebControl	End CreateChildControls()
118	CustomWebControl	End EnsureChildControls()
119	CustomWebControl	Start OnPreRender(EventArgs e)
120	CustomWebControl	End OnPreRender(EventArgs e)
121	aspx.page	End PreRender
122	aspx.page	Begin PreRenderComplete
123	Page	Start OnPreRenderComplete(EventArgs e)
124	Page	End OnPreRenderComplete(EventArgs e)
125	aspx.page	End PreRenderComplete
126	Page	SaveViewState()
127	MasterPage	SaveViewState()
128	UserControl	SaveViewState()
129	CustomWebControl	SaveViewState()
130	aspx.page	Begin SaveState
131	Page	SaveViewState()
132	Page	HasControls()
133	MasterPage	SaveViewState()
134	MasterPage	HasControls()
135	UserControl	SaveViewState()
136	UserControl	HasControls()
137	CustomWebControl	SaveViewState()
138	CustomWebControl	HasControls()
139	Page	Start SavePageStateToPersistenceMedium(object state)
140	Page	ResolveAdapter()
141	Page	End SavePageStateToPersistenceMedium(object state)
142	aspx.page	End SaveState
143	aspx.page	Begin SaveStateComplete
144	Page	Start OnSaveStateComplete(EventArgs e)
145	Page	End OnSaveStateComplete(EventArgs e)
146	aspx.page	End SaveStateComplete
147	aspx.page	Begin Render
148	Page	CreateHtmlTextWriter(System.IO.TextWriter tw)
149	Page	Start RenderControl(HtmlTextWriter writer)
150	Page	Start Render(HtmlTextWriter writer)
151	Page	Start RenderChildren(HtmlTextWriter writer)
152	MasterPage	Start RenderControl(HtmlTextWriter writer)
153	MasterPage	Start Render(HtmlTextWriter writer)
154	MasterPage	Start RenderChildren(HtmlTextWriter writer)
155	UserControl	Start RenderControl(HtmlTextWriter writer)
156	UserControl	Start Render(HtmlTextWriter writer)
157	UserControl	Start RenderChildren(HtmlTextWriter writer)
158	UserControl	End RenderChildren(HtmlTextWriter writer)
159	UserControl	End Render(HtmlTextWriter writer)
160	UserControl	End RenderControl(HtmlTextWriter writer)
161	CustomWebControl	Start RenderControl(HtmlTextWriter writer)
162	CustomWebControl	Start Render(HtmlTextWriter writer)
163	CustomWebControl	Start RenderBeginTag(HtmlTextWriter writer)
164	CustomWebControl	Start AddAttributesToRender(HtmlTextWriter writer)
165	CustomWebControl	End AddAttributesToRender(HtmlTextWriter writer)
166	CustomWebControl	End RenderBeginTag(HtmlTextWriter writer)
167	CustomWebControl	Start RenderContents(HtmlTextWriter writer)
168	CustomWebControl	Start RenderChildren(HtmlTextWriter writer)
169	CustomWebControl	End RenderChildren(HtmlTextWriter writer)
170	CustomWebControl	End RenderContents(HtmlTextWriter writer)
171	CustomWebControl	Start RenderEndTag(HtmlTextWriter writer)
172	CustomWebControl	End RenderEndTag(HtmlTextWriter writer)
173	CustomWebControl	End Render(HtmlTextWriter writer)
174	CustomWebControl	End RenderControl(HtmlTextWriter writer)
175	MasterPage	End RenderChildren(HtmlTextWriter writer)
176	MasterPage	End Render(HtmlTextWriter writer)
177	MasterPage	End RenderControl(HtmlTextWriter writer)
178	Page	End RenderChildren(HtmlTextWriter writer)
179	Page	End Render(HtmlTextWriter writer)
180	Page	End RenderControl(HtmlTextWriter writer)
181	aspx.page	End Render
182	Page	CreateHtmlTextWriter(System.IO.TextWriter tw)

reference : Complete Lifecycle of an ASP.Net page and controls

Custom webpart properties

AwardView.cs

[ToolboxItemAttribute(false)]
public class AwardView : WebPart
{
    [WebBrowsable(true)]
    [Category("Extended")]
    [WebDescription("Shows when items contains no photo")]
    [WebDisplayName("Substitute Photo")]
    [Personalizable(PersonalizationScope.Shared)]
    public string SubstitutePhoto { get; set; }
 
    // Visual Studio might automatically update this path when you change the Visual Web Part project item.
    private const string _ascxPath = @"~/_CONTROLTEMPLATES/QuarterlyAward/AwardView/AwardViewUserControl.ascx";
 
    protected override void CreateChildControls()
    {
        Control control = Page.LoadControl(_ascxPath);
        Controls.Add(control);
    }
}

AwardView.webpart (set default value)

<?xml version="1.0" encoding="utf-8"?>
<webParts>
  <webPart xmlns="http://schemas.microsoft.com/WebPart/v3">
    <metaData>
      <type name="QuarterlyAward.AwardView.AwardView, $SharePoint.Project.AssemblyFullName$" />
      <importErrorMessage>$Resources:core,ImportErrorMessage;</importErrorMessage>
    </metaData>
    <data>
      <properties>
        <property name="Title" type="string">AwardView</property>
        <property name="Description" type="string">My Visual WebPart</property>
        <property name="SubstitutePhoto" type="string">/sites/BruceDev/SiteAssets/Personal.png</property>
      </properties>
    </data>
  </webPart>
</webParts>

Precautions of sharepoint performance issue

1. Iterating through SPList Items
2. Requesting too much data from the content database
3. Memory Leaks with SPSite and SPWeb
4. Index Columns are not necessarily improving performance
5. SharePoint is not a relational database for high volume transactional processing

How to avoid the Top 5 SharePoint Performance Mistakes

Sharepoint list field

FieldType	Class Type	Type Display Name
Attachments	Microsoft.SharePoint.SPFieldAttachments	Attachments
Boolean	Microsoft.SharePoint.SPFieldBoolean	Yes/No
Calculated	Microsoft.SharePoint.SPFieldCalculated	Calculated
Choice	Microsoft.SharePoint.SPFieldChoice	Choice
Computed	Microsoft.SharePoint.SPFieldComputed	Computed
ContentTypeId	Microsoft.SharePoint.SPField	Content Type Id
Counter	Microsoft.SharePoint.SPField	Counter
Currency	Microsoft.SharePoint.SPFieldCurrency	Currency
DateTime	Microsoft.SharePoint.SPFieldDateTime	Date and Time
File	Microsoft.SharePoint.SPFieldFile	File
Guid	Microsoft.SharePoint.SPFieldGuid	Guid
Integer	Microsoft.SharePoint.SPFieldNumber	Integer
Lookup	Microsoft.SharePoint.SPFieldLookup	Lookup
ModStat	Microsoft.SharePoint.SPFieldModStat	Moderation Status
Note	Microsoft.SharePoint.SPFieldMultiLineText	Multiple lines of text
Number	Microsoft.SharePoint.SPFieldNumber	Number
Text	Microsoft.SharePoint.SPFieldText	Single line of text
URL	Microsoft.SharePoint.SPFieldUrl	Hyperlink or Picture
User	Microsoft.SharePoint.SPFieldUser	Person or Group
UserMulti	Microsoft.SharePoint.SPFieldUser	Person or Group

Get SPUser from Pserson or Group Column

SPUser GetSPUser(SPField field, object value)
{
 SPFieldUser userField = field as SPFieldUser;
 if (userField == null) return null;
 SPFieldUserValue fieldValue = userField.GetFieldValue(value as string) as SPFieldUserValue;
 if (fieldValue == null) return null;
 SPUser user = fieldValue.User;
 return user;
}

Calculated Field Formulas

Conditional formulas

You can use the following formulas to test the condition of a statement and return a Yes or No value, to test an alternate value such as OK or Not OK, or to return a blank or dash to represent a null value.

Determine whether a number is greater than or less than another number

Use the IF function to perform this comparison.

Column1	Column2	Formula	Description (possible result)
15000	9000	=[Column1]>[Column2]	Is Column1 greater than Column2? (Yes)
15000	9000	=IF([Column1]<=[Column2], "OK", "Not OK")	Is Column1 less than or equal to Column2? (Not OK)

Return a logical value after comparing column contents

For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.

Column1	Column2	Column3	Formula	Description (possible result)
15	9	8	=AND([Column1]>[Column2], [Column1]<[Column3])	Is 15 greater than 9 and less than 8? (No)
15	9	8	=OR([Column1]>[Column2], [Column1]<[Column3])	Is 15 greater than 9 or less than 8? (Yes)
15	9	8	=NOT([Column1]+[Column2]=24)	Is 15 plus 9 not equal to 24? (No)

For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.

Column1	Column2	Column3	Formula	Description (possible result)
15	9	8	=IF([Column1]=15, "OK", "Not OK")	If the value in Column1 equals 15, return "OK". (OK)
15	9	8	=IF(AND([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK")	If 15 is greater than 9 and less than 8, return "OK". (Not OK)
15	9	8	=IF(OR([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK")	If 15 is greater than 9 or less than 8, return "OK". (OK)

Display zeroes as blanks or dashes

To display a zero, perform a simple calculation. To display a blank or a dash, use the IF function.

Column1	Column2	Formula	Description (possible result)
10	10	=[Column1]-[Column2]	Second number subtracted from the first. (0)
15	9	=IF([Column1]-[Column2],[Column1]-[Column2],"-")	Returns a dash when the value is zero. (-)

Hide error values in columns

To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.

Column1	Column2	Formula	Description (possible result)
10	0	=[Column1]/[Column2]	Results in an error (#DIV/0)
10	0	=IF(ISERROR([Column1]/[Column2]),"NA",[Column1]/[Column2])	Returns NA when the value is an error
10	0	=IF(ISERROR([Column1]/[Column2]),"-",[Column1]/[Column2])	Returns a dash when the value is an error

Date and time formulas

You can use the following formulas to perform calculations that are based on dates and times, such as adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.

Add dates

To add a number of days to a date, use the addition (+) operator.

Column1	Column2	Formula	Description (possible result)
6/9/2007	3	=[Column1]+[Column2]	Adds 3 days to 6/9/2007 (6/12/2007)
12/10/2008	54	=[Column1]+[Column2]	Adds 54 days to 12/10/2008 (2/2/2009)

To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1	Column2	Formula	Description (possible result)
6/9/2007	3	=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1]))	Adds 3 months to 6/9/2007 (9/9/2007)
12/10/2008	25	=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1]))	Adds 25 months to 12/10/2008 (1/10/2011)

To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1	Column2	Formula	Description (possible result)
6/9/2007	3	=DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1]))	Adds 3 years to 6/9/2007 (6/9/2010)
12/10/2008	25	=DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1]))	Adds 25 years to 12/10/2008 (12/10/2033)

To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1	Formula	Description (possible result)
6/9/2007	=DATE(YEAR([Column1])+3,MONTH([Column1])+1,DAY([Column1])+5)	Adds 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)
12/10/2008	=DATE(YEAR([Column1])+1,MONTH([Column1])+7,DAY([Column1])+5)	Adds 1 year, 7 months, and 5 days to 12/10/2008 (7/15/2010)

Calculate the difference between two dates

Use the DATEDIF function to perform this calculation.

Column1	Column2	Formula	Description (possible result)
01-Jan-1995	15-Jun-1999	=DATEDIF([Column1], [Column2],"d")	Returns the number of days between the two dates (1626)
01-Jan-1995	15-Jun-1999	=DATEDIF([Column1], [Column2],"ym")	Returns the number of months between the dates, ignoring the year part (5)
01-Jan-1995	15-Jun-1999	=DATEDIF([Column1], [Column2],"yd")	Returns the number of days between the dates, ignoring the year part (165)

Calculate the difference between two times

To present the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function. For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

Column1	Column2	Formula	Description (possible result)
06/09/2007 10:35 AM	06/09/2007 3:30 PM	=TEXT([Column2]-[Column1],"h")	Hours between two times (4)
06/09/2007 10:35 AM	06/09/2007 3:30 PM	=TEXT([Column2]-[Column1],"h:mm")	Hours and minutes between two times (4:55)
06/09/2007 10:35 AM	06/09/2007 3:30 PM	=TEXT([Column2]-[Column1],"h:mm:ss")	Hours, minutes, and seconds between two times (4:55:00)

To present the result in a total that is based on one time unit, use the INT function, or HOUR, MINUTE, or SECOND function.

Column1	Column2	Formula	Description (possible result)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=INT(([Column2]-[Column1])*24)	Total hours between two times (28)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=INT(([Column2]-[Column1])*1440)	Total minutes between two times (1735)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=INT(([Column2]-[Column1])*86400)	Total seconds between two times (104100)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=HOUR([Column2]-[Column1])	Hours between two times, when the difference does not exceed 24 (4)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=MINUTE([Column2]-[Column1])	Minutes between two times, when the difference does not exceed 60 (55)
06/09/2007 10:35 AM	06/10/2007 3:30 PM	=SECOND([Column2]-[Column1])	Seconds between two times, when the difference does not exceed 60 (0)

Convert times

To convert hours from the standard time format to a decimal number, use the INT function.

Column1	Formula	Description (possible result)
10:35 AM	=([Column1]-INT([Column1]))*24	Number of hours since 12:00 AM (10.583333)
12:15 PM	=([Column1]-INT([Column1]))*24	Number of hours since 12:00 AM (12.25)

To convert hours from a decimal number to the standard time format (hours:minutes:seconds), use the division operator and the TEXT function.

Column1	Formula	Description (possible result)
23:58	=TEXT(Column1/24, "hh:mm:ss")	Hours, minutes, and seconds since 12:00 AM (00:59:55)
2:06	=TEXT(Column1/24, "h:mm")	Hours and minutes since 12:00 AM (0:05)

Insert Julian dates

A Julian date refers to a date format that is a combination of the current year and the number of days since the beginning of the year. For example, January 1, 2007, is represented as 2007001 and December 31, 2007, is represented as 2007365. This format is not based on the Julian calendar.

To convert a date to a Julian date, use the TEXT and DATEVALUE functions.

Column1	Formula	Description (possible result)
6/23/2007	=TEXT([Column1],"yy")&TEXT(([Column1]-DATEVALUE("1/1/"& TEXT([Column1],"yy"))+1),"000")	Date in Julian format, with a two-digit year (07174)
6/23/2007	=TEXT([Column1],"yyyy")&TEXT(([Column1]-DATEVALUE("1/1/"&TEXT([Column1],"yy"))+1),"000")	Date in Julian format, with a four-digit year (2007174)

To convert a date to a Julian date that is used in astronomy, use the constant 2415018.50. This formula works only for dates after 3/1/1901, and if you are using the 1900 date system.

Column1	Formula	Description (possible result)
6/23/2007	=[Column1]+2415018.50	Date in Julian format, used in astronomy (2454274.50)

Show dates as the day of the week

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.

Column1	Formula	Description (possible result)
19-Feb-2007	=TEXT(WEEKDAY([Column1]), "dddd")	Calculates the day of the week for the date and returns the full name of the day (Monday)
3-Jan-2008	=TEXT(WEEKDAY([Column1]), "ddd")	Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)

Mathematical formulas

You can use the following formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers; calculating the average or median of numbers; rounding a number; and counting values.

Add numbers

To add numbers in two or more columns in a row, use the addition operator (+) or the SUM function.

Column1	Column2	Column3	Formula	Description (possible result)
6	5	4	=[Column1]+[Column2]+[Column3]	Adds the values in the first three columns (15)
6	5	4	=SUM([Column1],[Column2],[Column3])	Adds the values in the first three columns (15)
6	5	4	=SUM(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3])	If Column1 is greater than Column2, adds the difference and Column3. Else add 10 and Column3 (5)

Subtract numbers

To subtract numbers in two or more columns in a row, use the subtraction operator (-) or the SUM function with negative numbers.

Column1	Column2	Column3	Formula	Description (possible result)
15000	9000	-8000	=[Column1]-[Column2]	Subtracts 9000 from 15000 (6000)
15000	9000	-8000	=SUM([Column1], [Column2], [Column3])	Adds numbers in the first three columns, including negative values (16000)

Calculate the difference between two numbers as a percentage

Use the subtraction (-) and division (/) operators and the ABS function.

Column1	Column2	Formula	Description (possible result)
2342	2500	=([Column2]-[Column1])/ABS([Column1])	Percentage change (6.75% or 0.06746)

Multiply numbers

To multiply numbers in two or more columns in a row, use the multiplication operator (*) or the PRODUCT function.

Column1	Column2	Formula	Description (possible result)
5	2	=[Column1]*[Column2]	Multiplies the numbers in the first two columns (10)
5	2	=PRODUCT([Column1], [Column2])	Multiplies the numbers in the first two columns (10)
5	2	=PRODUCT([Column1],[Column2],2)	Multiplies the numbers in the first two columns and the number 2 (20)

Divide numbers

To divide numbers in two or more columns in a row, use the division operator (/).

Column1	Column2	Formula	Description (possible result)
15000	12	=[Column1]/[Column2]	Divides 15000 by 12 (1250)
15000	12	=([Column1]+10000)/[Column2]	Adds 15000 and 10000, and then divides the total by 12 (2083)

Calculate the average of numbers

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

Column1	Column2	Column3	Formula	Description (possible result)
6	5	4	=AVERAGE([Column1], [Column2],[Column3])	Average of the numbers in the first three columns (5)
6	5	4	=AVERAGE(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3])	If Column1 is greater than Column2, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3 (2.5)

Calculate the median of numbers

The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.

A	B	C	D	E	F	Formula	Description (result)
10	7	9	27	0	4	=MEDIAN(A, B, C, D, E, F)	Median of numbers in the first 6 columns (8)

Calculate the smallest or largest number in a range

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.

Column1	Column2	Column3	Formula	Description (possible result)
10	7	9	=MIN([Column1], [Column2], [Column3])	Smallest number (7)
10	7	9	=MAX([Column1], [Column2], [Column3])	Largest number (10)

Count values

To count numeric values, use the COUNT function.

Column1	Column2	Column3	Formula	Description (possible result)
Apple		12/12/2007	=COUNT([Column1], [Column2], [Column3])	Counts the number of columns that contain numeric values. Excludes date and time, text, and null values (0)
$12	#DIV/0!	1.01	=COUNT([Column1], [Column2], [Column3])	Counts the number of columns that contain numeric values, but excludes error and logical values (2)

Increase or decrease a number by a percentage

Use the percent (%) operator to perform this calculation.

Column1	Column2	Formula	Description (possible result)
23	3%	=[Column1]*(1+5%)	Increases number in Column1 by 5% (24.15)
23	3%	=[Column1]*(1+[Column2])	Increases number in Column1 by the percent value in Column2: 3% (23.69)
23	3%	=[Column1]*(1-[Column2])	Decreases number in Column1 by the percent value in Column2: 3% (22.31)

Raise a number to a power

Use the exponentiation operator (^) or the POWER function to perform this calculation.

Column1	Column2	Formula	Description (possible result)
5	2	=[Column1]^[Column2]	Calculates five squared (25)
5	3	=POWER([Column1], [Column2])	Calculates five cubed (125)

Round a number

To round up a number, use the ROUNDUP, ODD, or EVEN function.

Column1	Formula	Description (possible result)
20.3	=ROUNDUP([Column1],0)	Rounds 20.3 up to the nearest whole number (21)
-5.9	=ROUNDUP([Column1],0)	Rounds -5.9 up to the nearest whole number (-5)
12.5493	=ROUNDUP([Column1],2)	Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)
20.3	=EVEN([Column1])	Rounds 20.3 up to the nearest even number (22)
20.3	=ODD([Column1])	Rounds 20.3 up to the nearest odd number (21)

To round down a number, use the ROUNDDOWN function.

Column1	Formula	Description (possible result)
20.3	=ROUNDDOWN([Column1],0)	Rounds 20.3 down to the nearest whole number (20)
-5.9	=ROUNDDOWN([Column1],0)	Rounds -5.9 down to the nearest whole number (-6)
12.5493	=ROUNDDOWN([Column1],2)	Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)

To round a number to the nearest number or fraction, use the ROUND function.

Column1	Formula	Description (possible result)
20.3	=ROUND([Column1],0)	Rounds 20.3 down, because the fractional part is less than .5 (20)
5.9	=ROUND([Column1],0)	Rounds 5.9 up, because the fractional part is greater than .5 (6)
-5.9	=ROUND([Column1],0)	Rounds -5.9 down, because the fractional part is less than -.5 (-6)
1.25	=ROUND([Column1], 1)	Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)
30.452	=ROUND([Column1], 2)	Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.

Column1	Formula	Description (possible result)
5492820	=ROUND([Column1],3-LEN(INT([Column1])))	Rounds the number to 3 significant digits (5490000)
22230	=ROUNDDOWN([Column1],3-LEN(INT([Column1])))	Rounds the bottom number down to 3 significant digits (22200)
5492820	=ROUNDUP([Column1], 5-LEN(INT([Column1])))	Rounds the top number up to 5 significant digits (5492900)

Text formulas

You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

Change the case of text

To change the case of text, use the UPPER, LOWER, or PROPER function.

Column1	Formula	Description (possible result)
nina Vietzen	=UPPER([Column1])	Changes text to uppercase (NINA VIETZEN)
nina Vietzen	=LOWER([Column1])	Changes text to lowercase (nina vietzen)
nina Vietzen	=PROPER([Column1])	Changes text to title case (Nina Vietzen)

Combine first and last names

To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.

Column1	Column2	Formula	Description (possible result)
Carlos	Carvallo	=[Column1]&[Column2]	Combines the two strings (CarlosCarvallo)
Carlos	Carvallo	=[Column1]&" "&[Column2]	Combines the two strings, separated by a space (Carlos Carvallo)
Carlos	Carvallo	=[Column2]&", "&[Column1]	Combines the two strings, separated by a comma and a space (Carvallo, Carlos)
Carlos	Carvallo	=CONCATENATE([Column2], ",", [Column1])	Combines the two strings, separated by a comma (Carvallo,Carlos)

Combine text and numbers from different columns

To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.

Column1	Column2	Formula	Description (possible result)
Yang	28	=[Column1]&" sold "&[Column2]&" units."	Combines contents above into a phrase (Yang sold 28 units.)
Dubois	40%	=[Column1]&" sold "&TEXT([Column2],"0%")&" of the total sales."	Combines contents above into a phrase (Dubois sold 40% of the total sales.) Note   The TEXT function appends the formatted value of Column2 instead of the underlying value, which is 0.4.
Yang	28	=CONCATENATE([Column1]," sold ",[Column2]," units.")	Combines contents above into a phrase (Yang sold 28 units.)

Combine text with a date or time

To combine text with a date or time, use the TEXT function and the ampersand operator (&).

Column1	Column2	Formula	Description (possible result)
Billing Date	5-Jun-2007	="Statement date: "&TEXT([Column2], "d-mmm-yyyy")	Combines text with a date (Statement date: 5-Jun-2007)
Billing Date	5-Jun-2007	=[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy")	Combines text and date from different columns into one column (Billing Date Jun-05-2007)

Compare column contents

To compare one column to another column or a list of values, use the EXACT and OR functions.

Column1	Column2	Formula	Description (possible result)
BD122	BD123	=EXACT([Column1],[Column2])	Compares contents of first two columns (No)
BD122	BD123	=EXACT([Column1], "BD122")	Compares contents of Column1 and the string "BD122" (Yes)

Determine whether a column value or a part of it matches specific text

To determine whether a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.

Column1	Formula	Description (possible result)
Vietzen	=IF([Column1]="Vietzen", "OK", "Not OK")	Determines whether Column1 is Vietzen (OK)
Vietzen	=IF(ISNUMBER(FIND("v",[Column1])), "OK", "Not OK")	Determines whether Column1 contains the letter v (OK)
BD123	=ISNUMBER(FIND("BD",[Column1]))	Determines whether Column1 contains BD (Yes)

Count nonblank columns

To count nonblank columns, use the COUNTA function.

Column1	Column2	Column3	Formula	Description (possible result)
Sales	19		=COUNTA([Column1], [Column2])	Counts the number of nonblank columns (2)
Sales	19		=COUNTA([Column1], [Column2], [Column3])	Counts the number of nonblank columns (2)

Remove characters from text

To remove characters from text, use the LEN, LEFT, and RIGHT functions.

Column1	Formula	Description (possible result)
Vitamin A	=LEFT([Column1],LEN([Column1])-2)	Returns 7 (9-2) characters, starting from left (Vitamin)
Vitamin B1	=RIGHT([Column1], LEN([Column1])-8)	Returns 2 (10-8) characters, starting from right (B1)

Remove spaces from the beginning and end of a column

To remove spaces from a column, use the TRIM function.

Column1	Formula	Description (possible result)
Hello there!	=TRIM([Column1])	Removes the spaces from the beginning and end (Hello there!)

Repeat a character in a column

To repeat a character in a column, use the REPT function.

Formula	Description (possible result)
=REPT(".",3)	Repeats a period 3 times (...)
=REPT("-",10)	Repeats a dash 10 times (----------)

reference: Calculated Field Formulas