PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?SmFjayBXaWxzb24=?=
Guest
Posts: n/a
 
      17th Jul 2006
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


 
Reply With Quote
 
 
 
 
VBA Noob
Guest
Posts: n/a
 
      17th Jul 2006

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

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      18th Jul 2006
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
 
Reply With Quote
 
SteveW
Guest
Posts: n/a
 
      18th Jul 2006
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.
>

 
Reply With Quote
 
=?Utf-8?B?SmFjayBXaWxzb24=?=
Guest
Posts: n/a
 
      18th Jul 2006
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
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


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


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:26 AM.