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 


0
Wilson (38)
7/17/2006 10:11:02 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
681 Views

Similar Articles

[PageSpeed] 8

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?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56225

0
7/17/2006 10:41:32 PM
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
0
petersod (12005)
7/17/2006 11:18:09 PM
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  =

<Wilson@discussions.microsoft.com>> wrote:

> I have a spreadsheet that the data, the date was input in Excel 2003 a=
s
> 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 (ye=
s
> colons) format.
>
0
sj_walton (248)
7/18/2006 7:55:57 AM
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 
> 
> 
0
7/18/2006 12:57:02 PM
Reply:

Similar Artilces:

Change left bar and top bar names on Order screen
Ok, I guess this could be the left bar and top bar names for any screen, but I'm specifically asking about the Order screen. I have a client that wants to change "Create Invoice" at the top to "Close". They also want to change "Write-In Products" to "Write-In Rooms". Where do I change these? I don't see them as options in the customization screens, even in the N:1 and 1:N areas. On Sep 23, 2:18=A0pm, Wayne Walton <waynek...@gmail.com> wrote: > Ok, I guess this could be the left bar and top bar names for any > screen, but I'm ...

How do I change a budgeted amount without changing previous budgets?
A newbie question I'm sure. My rent changed this month. When I change the budgeted amount, it changes (trashes) my budget for all previous months. How do change a budgeted amount for ONLY a specific time period? Thanks. Steve in Seattle ...

Change Names of Legend
Hello, I understand that you change the actual text that appears in the legend, not the font of it. I tried to follow the example given on this site and doesn't work. Here is the formula for one of the markers in the Chart. The formula refers to 2 markers on the chart. I would like one marker in the legend to be "plastic" and the other "hardware" =SERIES(,Sheet1!$C$2:$C$28,Sheet1!$B$2:$B$28,1) Thank you very much for your help. This is for a presentation due on Monday. -- Thank you... Elaine Normally I wouldn't reply, but since the real experts may be of...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Change paths to linked external data
Hello. I have a database that has 3 external links in it: 1 is to a table in another Access Database. One is to an excel spread sheet. And, the third is to a .csv file. Is there a way in VBA to change the paths to these? Basically, i want to set up a form where the user can specify the new paths in text boxes if any of these files are moved. Anybody help me out? (Access 2007) cyb3rwolf wrote: >Hello. I have a database that has 3 external links in it: 1 is to a table >in another Access Database. One is to an excel spread sheet. And, the third >is to a .csv...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

Conditional Format order
Hi all, Is there an easy way of changing the order of the three conditional formats for a cell, or do I just have to re-type them all? Thanks - Dave. Sorry, no quick and easy way - just be glad there aren't more to deal with! Do remember that you can change groups of cells to same conditions, at least you don't have to do that one at a time. "davebonallack@hotmail.com" wrote: > Hi all, > Is there an easy way of changing the order of the three conditional > formats for a cell, or do I just have to re-type them all? > Thanks - Dave. > > You could open...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Pivot Table Axis format
Hi. I have a macro that creates a pivot-table and pivot chart. The X-axis is a date. In the raw data, the date is formated as month/day. This is the way I want it on the chart. However, the X-axis formats with month/day/year. How can I force this to display correctly? Thanks, Mike. Do you see a grey little dropdown under the X axis (where you can choose your dates to show)? If yes, then rightclick on it. Select the top option: "Format pivotchart field" then click on the Number button on the next dialog. There are a bunch of date formats that you can choose from. Maybe...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...

Is this possible within Excel 2000?
Hi, I've used Excel on a get by basis for many years at work, and I'm just beginning to realise its vast capabilities, having viewed several groups which I didn't know existed previously. I subsequently would like to ask if it is possible for Excel to look at the numerical or text content of a cell on one worksheet and then look at a predefined range on another worksheet and find the equivalent cell content and then move the cursor to that cell? This has me stumped presently. Thanks in advance for any assistance. J.J. To actually move the selection, you'd need to use a litt...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

how do I insert rows without changing formula
In a cashflow worksheet how can I add rows without having to re-do all the proceeding formulas. use absolute references e.g. =$A$5 -- HTH RP (remove nothere from the email address if mailing direct) "tinka" <tinka@discussions.microsoft.com> wrote in message news:4ED4569E-0FD6-469B-82B1-F30139B1A280@microsoft.com... > In a cashflow worksheet how can I add rows without having to re-do all the > proceeding formulas. ...

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

EXCEL 2007 acting up
j$ = "THIS MACRO WILL ERASE THE SECOND & THIRD SET OF DATA" + Chr$(13) + Chr$(13) Using EXCEL 2007, I get an error with the line above as a macro. It does not recognize CHR$. What am I doing wrong? Thanks Not for me it didn't. Check for MISSING references in Tools>References, uncheck them if there are any. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcor" <pcor@discussions.microsoft.com> wrote in message news:F63F6E95-AF0A-4927-B337-99B5C48B92B1@microsoft.com... > j$ = "THIS MACRO WILL...

how do you change the font size of a drop-down list?
When I use the Validation feature under Data Menu and I create a drop-down list, the font size of the contents of the drop-down list is too small to read. How do I change this? You can't change the font or font size for a Data Validation dropdown list. To make the font appear larger, you can use programming to adjust the zoom setting when the cell is selected. There's sample code here: http://www.contextures.com/xlDataVal08.html#Larger Or, there's a sample file here that provides a combobox from which you can select one of the values from the data validation list. In ...

problems loading excel documents
When I try to open up an excel document via my documents it says that the same document is open already how can this be stopped Tina First try the standard fix(es)......Tools>Options>General uncheck "ignore other applications" OR Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You may have to enter your full path to excel.exe....in that case surround with quotes as in..... "C:\mypath\to Excel\somewhere\excel.exe" /regserver Gord Dibben XL2002 On Sat, 15 Nov 2003 09:16:14 -0800, "Tina" <TinaSCovell...

Change Mail Recipient #2
In my Office 2003 Picture Manager, when I right click a picture to send to mail or go to File, Send To recipient it goes to Outlook. I wish it to change it to Outlook Express. I have gone to IE, Tools, Options, Programs, and E-mail and changed the default to Outlook Express. My Office Word, Excel, and PowerPoint does not go to Outlook they go to Express, it only goes to Outlook in Picture Manager. ...

setting a date type field to null
I have defined a variable in my vba code as date. I am trying to set its value to null but I get an error. Dim HoldDate as date Holddate = "" I have several other fields that are text that are obviously fine, but I am not sure how to set a default for the date type field. I need to initialize the field after I write a record out before processing new records Any help would be appreciated. Thanks, -- KSH On Thu, 13 Dec 2007 21:06:00 -0800, KSH wrote: > I have defined a variable in my vba code as date. I am trying to set its > value to null but I get an error. &g...

How do you distribute global changes? (format strings etc) GP 9.0
This one is stumping me for the moment. In brief, the client needs to be able to enter phone numbers that are not compatable with the canned Phone_Number format and data types. I was able to make the change on my test machine easily enough, but I am uncertain if there are any gotchas in the distribution process. I found a knowledge base article that tells me I will need to copy Forms.dic and Dynamics.vba to the client machines. But, does it matter what order I do that in as regards my other modifications? I have a .cnk file, as well as simple modifed forms and reports (with and witho...

how to convert word file into excel file
If any body know about how to convert MS Word file into MS Excel file please let me know. I am unable to do so. Help me Rizwan Wrote: > If any body know about how to convert MS Word file into MS Excel file > please > let me know. > I am unable to do so. > > Help me Hi Rizwan Try Copy and Paste -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467752 Dear Pau...