Excel - V

Dates in Excel


You will find that Dates and Times in Excel can often seem confusing! But they often play a critical part in most spreadsheets so it is important that we have an understanding of how Excel interprets them.

How we enter dates in Excel is extremely important. If we do not insert a date in a valid form, Excel will not know that it is a date. One should be able to tell immediately if a date we enter is valid or not as Excel will align a valid date to the right of your cell (Number) and align a possible invalid date to the left (Text). When Excel recognizes a date as a valid date it will change from the General format (default for all cells) to a built-in date format.

When we enter dates on a Worksheet we must also use a valid date separator, such as 03/05/2010 or 03-05-10. If we want our date to look different to this we must format it after we have entered it. Lets try a couple of simple exercises to practice what we have learnt.
In cell A1 type: 03/05/2010 or 03/05/10. Both are valid.

Select Cell A1 and right click, then select Format Cells and then click the Number tab. This dialog box is the Format Cells dialog box. This is where we can apply formatting to both the cells themselves and the data contained within them. Click General under Categories. Now look in the Sample: box (top right). You should see the number: 40301. This is the number for our date: 03/05/2010 . This is because the this date is 40301 days from the 1/1/1900. Now select Date from within the Categories: box.Click through all the different dates within the Type: box and see the result in the Sample:: box. Select the format 3/14 and then click OK
Your date should now appear as 5/3. I say appear because while we may have changed its appearance we have NOT altered its underlying value which is 03/05/2010 or 40301. If you are still selected in cell A1 look in the formula bar and you should see 03/05/2010. So while it may appear we have dropped the year from the date we have not! In fact it is not possible to enter a valid date that has no Year, Day or Month. We could in fact format this cell to appear as 1/1/1989 and still have the true value of 03/05/2010 or 40301.

If we enter a date into Excel and omit the day eg; Dec-2009 Excel will recognize this as a valid date, but you should be aware that Excel will (by default) assign the first day of the month to the date. This means that while you may only see Dec-2009 in the cell, the underlying value of the date will be 12/1/2009 or 40148. This means that it is not possible to have a valid date in Excel that does not have a day of the month assigned to it.



We will discuss about TIME tomorrow as its time to say Goodbye!