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

's Computer Specifications

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

's Computer Specifications

How about

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

assuming date in E1

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56225

's Computer Specifications

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

's Computer Specifications

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
<(E-Mail Removed)>> 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 havea
> spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
> colons) format.
>

's Computer Specifications

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
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How 2 change date format - dd/mm/yyyy instead of mm/dd/yyyy	Dan Moore	Microsoft Outlook Discussion	1	4th Mar 2008 10:28 AM
change date format from dd/mm/yyyy to mm/yyyy	=?Utf-8?B?ZmxvdzIz?=	Microsoft Excel Misc	2	9th Nov 2005 03:33 PM
Change Date Format from(YYYY-MM-DD) to (MM-DD-YYYY)	DustinS	Microsoft Access	4	31st Mar 2005 03:31 AM
opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY	yung	Microsoft Excel Programming	2	18th Mar 2005 12:50 PM
Date format : field set to dd/mm/yyyy, but query wants mm/dd/yyyy	aa	Microsoft Access Queries	2	29th Dec 2003 10:53 AM