Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
How do I change a date fro 22 sept 09 to September 22, 2009 | New Users to Excel | |||
how can subtact or add Week 07/2009 in one column & Week 10/2009 . | Excel Worksheet Functions | |||
How create 365 labels for 2009? Example: Monday, Jan. 4, 2009 | Excel Discussion (Misc queries) | |||
Cell won't accept date past year 2009 | New Users to Excel |