Simple formatting of cells?

[Geebee]

(using Excel 2000) I want to format a column to enter a date like this 08/22/02 without having to type the "/", but when I format the column and type in 082002 without the slash, I get 07/05/24. I've used the custom formatting and still get the same 07/05/24. I tho't I could format a column for date without having to enter the slash. What's going on?

 

[PaddyD]

Hi - welcome to the board.

You can custom format date values as DDMMYYYY to get them displayed as you want, but you can't get away without entering the slashes or an equivalent. Why? 'cos excel needs to know what you are entering is a date, not a number - it's to do with how excel recognises date values (see the help file for 'How excel stores dates & times' for details). Excel stores dates & times as numbers - 1 for 1st Jan , 1900 - 2 for 2nd Jan, 1900 and so on. So if you enter a plain 082002, excel will recognise this as the number 82002, which is the date value for 5th July, 2124. The DDMMYYYY format will display this as 05072124.

It is possible to get round this - a formula approach would put your results in another cell. Format cell A1 as text. With 082002 in A1,

=DATE("20"&RIGHT(A1,2),LEFT(A1,2), MID(A1,3,2))

will give you a date value - format as you like. (Note that I have hard coded the 20 because you only have 2 digit years - you should enter them as YYYY).

To get the results in the same cell, you'll need a VBA solution - have a search here, there's probably one about - post back if you need to .

Paddy
This message was edited by PaddyD on 2002-08-22 18:53

 

[Yogi Anand]

Hi Geebee:

Welcome to the Board!

In addition to what Paddy suggested, you can also use the following formula to convert 082002 to the date 08/20/02

=text(B2,"00-00-00")+0 ... this will for a 6 digit string consisting of two digits for the month, two digits for the day, and two digits for the year.

see the worksheet simulation

Book1
	A	B	C	D
1
2		082002	08/20/02
3
Sheet5

</SPAN>

Regards!

Yogi
This message was edited by Yogi Anand on 2002-08-22 20:24

 

[PaddyD]

=DATE("20"&RIGHT(A1,2),LEFT(A1,2), MID(A1,3,2))

vs.

=text(a1,"00-00-00")+0

It's quite obviously time I gave up this week!

 

[Yogi Anand]

Hi Paddy:

Your solution is perfectly fine -- my feeling is that if we can get a solution whether it takes a few key strokes more or less is quite immaterial -- what we feel comfortable with is what matters ... a few key strokes might mean a few milliseconds which does not make any difference in the bigger scheme of things. I know we do make a big fuss on the board which is the shortest and which is the most efficient solution -- in my opinion in general use in the real world, getting the job done counts. For this post, my idea was to provide an alternate to a satisfactory solution you had already provided.

Regards!

Yogi