Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
I have a column of dates in an excel spreadsheet in the format 05-04-06. How
do I the change dates to 05/04/06. I have tried formatting the cells but this does not work. Does anyone have a formula that can be used please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
Goldie
Ordinarily Excel would recognize that format as a date and change it the way you want automatically. I don't know the source of these dates but Excel apparently thinks of them as text. Try this with one such cell and see what you get. Say the cell is A1. In some out-of-the-way cell, type =A1*1. This should produce a 5-digit number. That is a date. Format that cell as you like and you should get what you want. There are other ways of doing this so if this doesn't work for you, post back. If it works, now you want to do the same to all such cells. Here's a quick way. In some out-of-the-way cell type "1" without the quotes. Now with that cell selected, click on Edit - Copy. Now select all the cells that you want changed. Click on Edit - PasteSpecial and select Multiply then OK. Then format all the cells like you want. HTH Otto "Goldie" wrote in message ... I have a column of dates in an excel spreadsheet in the format 05-04-06. How do I the change dates to 05/04/06. I have tried formatting the cells but this does not work. Does anyone have a formula that can be used please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change date format
Goldie
Alternate method.......... Select column of dates. DataText to columnsNextNextData Format TypeDate. Pick DMY or MDY and Finish. Gord Dibben MS Excel MVP On Sun, 11 Jun 2006 16:52:31 -0400, "Otto Moehrbach" wrote: Goldie Ordinarily Excel would recognize that format as a date and change it the way you want automatically. I don't know the source of these dates but Excel apparently thinks of them as text. Try this with one such cell and see what you get. Say the cell is A1. In some out-of-the-way cell, type =A1*1. This should produce a 5-digit number. That is a date. Format that cell as you like and you should get what you want. There are other ways of doing this so if this doesn't work for you, post back. If it works, now you want to do the same to all such cells. Here's a quick way. In some out-of-the-way cell type "1" without the quotes. Now with that cell selected, click on Edit - Copy. Now select all the cells that you want changed. Click on Edit - PasteSpecial and select Multiply then OK. Then format all the cells like you want. HTH Otto "Goldie" wrote in message ... I have a column of dates in an excel spreadsheet in the format 05-04-06. How do I the change dates to 05/04/06. I have tried formatting the cells but this does not work. Does anyone have a formula that can be used please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date in numeric format | New Users to Excel | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
I cannot change the date format to English canada | Excel Discussion (Misc queries) | |||
How change Excel default date format to something useful | Excel Discussion (Misc queries) | |||
change date function format in footer | Excel Worksheet Functions |