Dates and Excel

When teaching Excel classes, invariably the subject of dealing with dates crops up.

Excel can be used to calculate dates. Firstly though, you need to know how to enter the date so that Excel knows that a date is being used.

Type dates in the format of dd/mm/yy e.g. 03/12/12 for 3rd December 2012. Excel will automatically display as 03/2/12/2012. You can also use Dec 12, though Excel will actually store the date from the first of the month i.e. 01/12/2012

There is a neat trick to always enter the current date – type Ctrl and ; (the semi-colon).  Excel picks up the current date from your computer.

Try it yourself – in cell A2, type Ctrl ; – today’s date should appear e.g. 3/12/2012

In Cell B2 type in a future date (let’s say 25/12/2012)

In cell C2, type =B2-A2 The number of days between the two dates will appear, in this example, 22

In some older versions of Excel, a date may be displayed, for example it might display 23/01/1904  This look very peculiar, but here I need to explain about how dates are stored.

Way back in the early days of computers, the different manufacturers decided that to use dates, they must agree on “which” date to start from.  The majority of manufacturers chose 1st January 1900 to be date 1.  At the time of this blog, today’s date shows as 41,246 if changed to a number format.

Note: Some manufacturers chose to begin in 1904, so now and again if you share files between those computers and PC’s, then you may see that the dates are four years out!

To change the 1900 date into a number, simply click the comma in Home > Number and the number will be displayed.


Posted in Uncategorized Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>