Year wrong when formatting cell

Topic

New Reply

I format my cell using Format/Cells – I select Date and I use the first option *3/14/2001.

I then type in 61811 and it comes up 3/24/2069. Of course, I’m looking for 6/18/2011.

I am using MS Excel Home and Student 2007 version.
I verified that it’s set for English (United States), the long and short dates are correct (using today’s date just how I’d want them)…not sure what else to check.
If I enter 6/18/11, it formats it correctly. But its faster not to have to enter the /’s. On the version I use at work (NOT the Home and Student version), I would not have to enter the /’s to get it to format the way I need it to.

What else can I check?

Thanks!

Reply | Quote

Replies

Hi Ruth,

Behind the scenes, dates are just regular numbers. 1 is equal to Jan 1, 1900, 2 is Jan 2, 1900. Not surprisingly, 61811 days later is March 24, 2069. So that’s normal.

There must be some special format to allow you to enter a date without delimiters, but I don’t know how to do that. But if you can do it at work, you should be able to do it at home – there’s no difference in the Excel in the different packages, as far as I know.

(In that case, how do you handle something like 12311? Is that Jan 23, 2011 or Dec 3, 2011?)

Edit: The little research I’ve done online suggests that there’s no normal built-in way to enter dates the way you want, but that it can be done with VBA. So maybe somebody has added that to your Excel at work.

Reply | Quote

As bjulien alludes to, there is code for “quick entry” of dates (see for example MS MVP Chip Pearson’s site at: http://www.cpearson.com/excel/DateTimeEntry.htm)

I would presume the same thing bjulien suggests, that this code is in the workbooks you use at work. I would suspect it is a feature of particular workbooks, though perhaps it was made part of the default template. The workbooks with this “feature” should work on any computer or excel version if macros are enabled.

Steve

Reply | Quote