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:
- Input your dates into two cells on your spreadsheet
- Create a formula which subtracts the earlier date from the later date(e.g. =A2-A1)
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:
- Input your start date (in this example the order date)
- In another cell input the delay (in this example the lead time to delivery). This should be in days
- Create a formula to add the days to the date (e.g. =A1+A2)
- Format the result as a date, using either the Number group on the ribbon or the Format Cells dialog box.
