Saturday, January 01, 2005

New Year and Dates in Excel

Happy New Year!

Don't forget that Excel automically does some date formatting for you.
Here's the basic rules to be wary of.

Note for the Amercians the dates are round the right way - you can swap the month and day to get it all backward the way you like it. (I think the answers are the same.)

When entering dates Excel will complete partly entered dates. Eg if you type 15/5 in a cell and hit Enter then you will get the 15th of May 2005 not 3. If you wanted 3 then type =15/5 and hit enter. (Not in the same cell or you will get 3-Jan (in the year 1900. You could reformat this however because it is the correct information, just not the correct format).

Notice that if you type 15/5 and hit Enter that it comes up 15-May. However if you click on the cell you will see in the formula bar that it is 15/05/2005.

So the first thing to be wary of is that Excel will automatically complete the year in a partly entered date (day and month) - using the current year.
If you wanted 15 May 2004 then you would need to either edit the year in that cell or type 15/5/04 and hit Enter.

The next thing to look at is the automatic year cutoff.
This is when you enter a year in the yy format. (It is better to use yyyy and then the following problem will never occur) For example enter 15/5/04 as above and you will get 15 May 2004. This time Excel is using a rule based on 1929. If you enter any date after (and including) 1 Jan 1930 and enter "30" as the year then you will get the date between now and 1930. If you enter a date year between 05 and 29 inclusive then it wil automatically assume that it is future and give you 2005-20029 as entered.
Test this by typing 15/5/29 in a cell and hitting Enter, then try 15/5/31.

The trick to be careful of is when working with dates from between 2005 and 2030 and on into the future. Enter the full year date for any year after or including 2030 or you will get 1930 instead wieh potnetially disstrous results for your spreadsheet.


Posted by: Jethro | 1:04 AM |