Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell date format doesnt change Jon Excel Worksheet Functions 5 March 7th 06 05:52 PM
Date format not correct when you convert a CSV text file in Excel Scarab Excel Discussion (Misc queries) 2 November 16th 05 01:22 PM
date format in excel not in line with control panel regional setti GrahamR Excel Discussion (Misc queries) 3 August 2nd 05 06:48 PM
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 10:20 PM


All times are GMT +1. The time now is 10:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"