how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
 

I have a spreadsheet that the data, the date was input in Excel 2003 as
mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
"dd:mm:yyyy"(31:12:2004)? I tried to change date
format from "Format Cells----choose "date" or "custom" and tried different
options, but they wouldnt change the data already in the cells. I have a
spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
colons) format.

Thanks

Jack

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
 

How about

=TEXT(SUBSTITUTE(E1,":","/"),"dd/mm/yy")

assuming date in E1

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562255

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
 

How about a custom format of:

dd\:mm\:yyyy



Jack Wilson wrote:

I have a spreadsheet that the data, the date was input in Excel 2003 as
mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
"dd:mm:yyyy"(31:12:2004)? I tried to change date
format from "Format Cells----choose "date" or "custom" and tried different
options, but they wouldnt change the data already in the cells. I have a
spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
colons) format.

Thanks

Jack

--

Dave Peterson

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
 

What you've done should work

Don't forget it wont change the data.
So if you click on one of the date cells it will still say 12/31/2004
But the format will display the date on screen/print as you want.

Steve

On Mon, 17 Jul 2006 23:11:02 +0100, Jack Wilson <Jack
wrote:

I have a spreadsheet that the data, the date was input in Excel 2003 as
mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
"dd:mm:yyyy"(31:12:2004)? I tried to change date
format from "Format Cells----choose "date" or "custom" and tried
different
options, but they wouldnt change the data already in the cells. I have a
spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
colons) format.

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel
 

Thanks to all for the replies. I appreciate it. -Jack

"Jack Wilson" wrote:

I have a spreadsheet that the data, the date was input in Excel 2003 as
mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
"dd:mm:yyyy"(31:12:2004)? I tried to change date
format from "Format Cells----choose "date" or "custom" and tried different
options, but they wouldnt change the data already in the cells. I have a
spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
colons) format.

Thanks

Jack