Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Date
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Date
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 "newguy" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Date
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 "newguy" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Date
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. -- Regards, Tom Ogilvy "newguy" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Date
That is the issue. I figured out a way to work with the / so I am good.
Thanks for your suggestions Ron. "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "newguy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formatting help | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
Conditional Formatting, date. | Excel Worksheet Functions | |||
Despite formatting a column in Excel 2002 worksheet as Short Date. | Excel Discussion (Misc queries) |