The basics of dates in Excel

To start with, any date can be entered in a cell, using a slash or a hyphen to separate the parts of a date, and Excel normally recognises it as a date. If it does not, you will be frustrated to find a number appearing instead and any amount of retyping does not help. To resolve this, the cell or cells need to be formatted as dates. This is very simply done, right click on the cell, select “Format cells...” , in the number tab which would have been selected automatically, scroll down to “Date” and select the style you prefer. And do make sure that you have picked your location correctly. Since I am based in India, I pick “English (India)”. This gives the date first followed by the month. A location change to “English (United States)” gives the month first followed by the date.

To use as data, Excel converts all dates into numbers. The date 1^st January 1900 is the base date with the serial number “1”. And say 1^st January 2012 (when someone has wished me Happy New Year) has the serial number “40909”. To find out what the number of any date is, just type the date in a cell, and then change its format to number.

To find out the difference between dates, the simple minus sign is used. The two dates may be entered in two cells say in B6 and B7 and wherever the difference is to appear, the formula is used. Here it would be “=B6-B7”. Let us say B6 is 17^th February 2012 and B7 is 5^th February 2012. The result displayed is “12”.

The autofill function works equally well with dates. Thus selecting B6 and B7, and dragging down, gives a series of dates with a gap of 12 days. For the basics of autofill, click here for an earlier post: http://computertipsforbeginners.blogspot.in/2011/05/excel-speed-up-data-entry.html

Some simple and interesting formulae on dates are:

1. To find out what day of the week the date appearing in cell B6 is, type =TEXT(B6,"dddd"). “Friday” appears which is the full form of the day of the week. On the other hand using =TEXT(B6,"ddd") (i.e. one “d’ less in the formula) gives “Fri” which is the abbreviated day.

2. Today’s date is displayed with “=TODAY()”. It is to be remembered that this displays the current date whenever the Excel sheet is opened and is useful when using a countdown or calculating the day before or after a specific event.

3. And if the current date is merely to be entered as a fixed entry, without being changed each time the Excel file is opened, press CTRL+; (semi-colon).

(This post is in response to a specific request by a reader. I do hope the requirement is fully met. I would be happy to respond further on this or any other issue).