Format date to e.g. 2009.08.31
 

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Hi,
use this custom format

0"."00"."00

if this helps please click yes thanks

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Why do you need it in that format? If I knew how you where going to use the
the date then maybe I can suggest a different method.

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Thanks Eduardo..works perfectly. If you have a second, could you explain to
me exactly how the (")'s affect the formatting?

"Eduardo" wrote:

Hi,
use this custom format

0"."00"."00

if this helps please click yes thanks

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Jvs,

If you're doing this in code, it's:

Format(dateVariable or text, "yyyy.mm.dd")

If you're doing this with a worksheet formula, you want
=Text(Range, "yyyy.mm.dd")

John

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

JVS, please note that this is only displayed in this format (text format).
Excel do not treat this as a date entry.....

"jvs" wrote:

Thanks Eduardo..works perfectly. If you have a second, could you explain to
me exactly how the (")'s affect the formatting?

"Eduardo" wrote:

Hi,
use this custom format

0"."00"."00

if this helps please click yes thanks

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Hi,
the "" are involving the dot, for example try a new custom format from this
one and between the " enter / instead of the . so now your date will look
like 2009/08/31

"jvs" wrote:

Thanks Eduardo..works perfectly. If you have a second, could you explain to
me exactly how the (")'s affect the formatting?

"Eduardo" wrote:

Hi,
use this custom format

0"."00"."00

if this helps please click yes thanks

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

Thanks everyone for all your very helpful information!! Much appreciated!

"Exceleration" wrote:

Jvs,

If you're doing this in code, it's:

Format(dateVariable or text, "yyyy.mm.dd")

If you're doing this with a worksheet formula, you want
=Text(Range, "yyyy.mm.dd")

John

"jvs" wrote:

I can format the date to display this way if we enter it as a date (e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..

Format date to e.g. 2009.08.31
 

If you wanted it converting to a date, try =--TEXT(A1,"0000\/00\/00") and
format as yyyy.mm.dd
--
David Biddulph

"Jacob Skaria" wrote in message
...
JVS, please note that this is only displayed in this format (text format).
Excel do not treat this as a date entry.....

"jvs" wrote:

Thanks Eduardo..works perfectly. If you have a second, could you explain
to
me exactly how the (")'s affect the formatting?

"Eduardo" wrote:

Hi,
use this custom format

0"."00"."00

if this helps please click yes thanks

"jvs" wrote:

I can format the date to display this way if we enter it as a date
(e.g.
8/4/2009) etc.

Is there a way to enter 20090831 (no punctuation) and still have it
display
as 2009.08.31?

I've tried custom format ####.##.## - it comes out as 20090831..