Categories
Excel Training

Excel training:Calculating with Dates

One of the most asked questions during our Excel 2010 Training Courses has to be the subject of calculating with dates.

Excel stores dates (and times) as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day (e.g. 31/10/2011 10:00 is stored as 40847.42).This is called a serial date, or serial date-time.

To calculate the difference between two dates:

Method – Subtract the earlier date from the later one:

  1. Input your dates into two cells on your spreadsheet
  2. Create a formula which subtracts the earlier date from the later date(e.g. =A2-A1)
Sample Date Difference Calculation
Sample Date Difference Calculation

3.  Format the result to be a number with no decimal places, using either the Number group on the ribbon  or Format Cells dialog box

To calculate a date:

Method – Add the lead time to the start date.  This method can be used to calculate anticipated delivery or payment dates.  The worked example below relates to a delivery date:

  1. Input your start date (in this example the order date)
  2. In another cell input the delay (in this example the lead time to delivery).  This should be in days
  3. Create a formula to add the days to the date (e.g. =A1+A2)
  4. Format the result as a date, using either the Number group on the ribbon  or the Format Cells dialog box.
Sample Date Calculation
Sample Date Calculation

 

 

Categories
Excel Training

How to Merge Cells in Excel 2010

Merge Cells in Excel 2010;

Study the spreadsheet below:

An Excel 2007 Spreadsheet

If you look at Row 1, you’ll see that the “Shopping Bill” heading stretches across three cells. This is not three separate cells, with a colour change for each individual cell. The A1, B1 and C1 cells were merged. To merge cells, do the following.

  • Type the words Shopping Billinto cell A1 of a spreadsheet
  • Highlight the cells A1, B1 and C1
  • On the Alignment panel of the Excel Ribbon, locate the “Merge and Center” item:

Merge and Centre

  • Click the down arrow to see the following options:

Merge Options in Excel 2007

Click on “Merge and Center”. Your three cells will then become one – A1, to be exact!