Excel short date format not working

[domino3]

I'm having problems inputting dates to my excel spreadsheet. I'm pretty sure ( but am starting to doubt my memory) that, in the past, I've been able to format cells in my spreadsheets as short dates which meant I could input 010101 and the cell then showed the date as 01/01/01 without me having had to input the forward slashes. Now when I input 010101 I get 27/08/27 with the forward slashes in. There is probably a really simple solution to this but I haven't been able to find it. I've checked the formatting of the cells and it shows as a date field with a short date format, and I've looked in Tools - options as well, but couldn't see anything that might be causing the problem. I've checked my regional and langauge options and they show as a short date as well. I wondered if it could be the individual sheet I was working on, but I tried it on a new worksheet as a test and it happened on that as well. Any help would be much appreciated.

 

[GlennUK]

Your memory sounds wrong. It sounds as though there was some kind of Worksheet_Change event macro being used to convert the input automatically, and NOT a formatting "trick" as you seem to think it was.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

there is no formatting trick to do that - htere must be code running somewhere to make the change

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Before you go too far, read and understand faq68-5827.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[cheerio]

Dates are stored by Excel as the number of days since the year 1900.

How they appear is a formatting issue.

This is why we can do arithmetic with dates. For example, if cell A1 holds a date then a cell with the formula =A1+1 will show the next day.

10101 days into the 20th century was the date 27th August 1927 which will appear as 27/08/27 if the cell is formatted as dd/mm/yy (a UK style date) or as yy/mm/dd.

As a general rule you should format to show the century.

Read the FAQ as advised to get a proper understanding.

 

[Fenrirshowl]

You could set a custom format of 00"/"00"/"00 but this would mean that it is input as text rather than a date, which I would say is a bad idea. (Especially when you consider it is only a case of throwing in a couple of /s while you type the rest in)

 

[domino3]

Thanks for all your replies. Obviously, my memory is completely at fault. I must have been getting confused with the date input masks you can put in access databases.

Thanks again.

 

[SkipVought]

domino3,

You have posted 94 questions since May 2003, and yet have only THANKED members for their valuable posts only SIX times. Were 93.6% of them THAT worthless? Does not synch with my experience.

The STARS not only point out to other members who are looking for good information, post that might be worthy of review, but they also show that you are a member who appreciates such good information, and is willing to so express.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]