Formatting Date

[Guest]

I have a column that contains dates in this format mm/dd/yyyy. I need
mmddyyyy no slashs. If I do a custom format it still shows mm/dd/yyyy in the
the fx bar. I have tried using vbscipt replace method and copy the new date
to a different column but it drops the zeros no matter what the columns
format is. I need to create passwords from this column and I have to take out
the slashes.

 

[Guest]

If your input is an actual date (9/16/2006)
then a custom format of MMDDYYYY will display: 09162006

But, if your input is 09162006
then Excel, not recognizing the value as a date,
will remove the leading zero and display: 9162006

For display purposes, you could create
a custom format of 00000000
which displays 9162006 as 09162006
(The leading zero would not actually exist,
but it would display)

OR
You could set the number format to TEXT
so Excel will retain and display anything you input

OR
You could just prefix your input with an apostrophe.
Consequently, '09162006 would be treated as text
and display as 09162006

Is any of of that rambling something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

One more thought.....

For a date in A1

You might be able to use this formula to achieve what you want:
B1: =TEXT(A1,"MMDDYYYY")

If A1 contains the date 9/16/2006
B1 returns 09162006 (as text)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Tom Ogilvy]

The underlying cell has to contain a date for custom formatting to work.
The custom formatting doesn't change the underlying value stored in the
cell.

 

[Guest]

That is the issue. I figured out a way to work with the / so I am good.
Thanks for your suggestions Ron.