Text to dates

I have a column of dates F6:F148, the dates have been entered in the 
following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" & 
"21 Jan 2009".  

I'd like to be able to change them all into a common date format 21 Jan 09 
(I'll also change the sheet so that data is validated to this date format)

Does anyone know a way I can do this?

Thanks
0
Utf
11/25/2009 3:20:03 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
392 Views

Similar Articles

[PageSpeed] 24

You can use the 'Convert Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected. 

--From menu Data>'Text to Columns' will populate the 'Convert Text to Columns 
Wizard'.

--Hit Next>Next will take you to Step 3 of 3 of the Wizard. 

--From 'Column Data format' select 'Date' and select the date format in which 
your data is ('DMY' )

--Hit Finish. MSExcel will now convert the dates to the default date format 
of your computer.

If this post helps click Yes
---------------
Jacob Skaria


"Topher" wrote:

> I have a column of dates F6:F148, the dates have been entered in the 
> following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" & 
> "21 Jan 2009".  
> 
> I'd like to be able to change them all into a common date format 21 Jan 09 
> (I'll also change the sheet so that data is validated to this date format)
> 
> Does anyone know a way I can do this?
> 
> Thanks
0
Utf
11/25/2009 3:28:01 PM
A number of options some of which *might* work:
1    Copy a blank cell, Select your data range, Edit/ Paste special/ Add
2    Select your column of data, then Data/ Text to Columns

Then format the cells appropriately.
-- 
David Biddulph

"Topher" <Topher@discussions.microsoft.com> wrote in message 
news:83CC7FF1-01F4-4CDE-A2C4-99D2637697A4@microsoft.com...
>I have a column of dates F6:F148, the dates have been entered in the
> following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" 
> &
> "21 Jan 2009".
>
> I'd like to be able to change them all into a common date format 21 Jan 09
> (I'll also change the sheet so that data is validated to this date format)
>
> Does anyone know a way I can do this?
>
> Thanks 


0
David
11/25/2009 3:43:27 PM
Sorry but  this does not work.

"Jacob Skaria" wrote:

> You can use the 'Convert Text to Columns Wizard to convert the dates
> 
> --Select the range of dates which needs to be corrected. 
> 
> --From menu Data>'Text to Columns' will populate the 'Convert Text to Columns 
> Wizard'.
> 
> --Hit Next>Next will take you to Step 3 of 3 of the Wizard. 
> 
> --From 'Column Data format' select 'Date' and select the date format in which 
> your data is ('DMY' )
> 
> --Hit Finish. MSExcel will now convert the dates to the default date format 
> of your computer.
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "Topher" wrote:
> 
> > I have a column of dates F6:F148, the dates have been entered in the 
> > following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" & 
> > "21 Jan 2009".  
> > 
> > I'd like to be able to change them all into a common date format 21 Jan 09 
> > (I'll also change the sheet so that data is validated to this date format)
> > 
> > Does anyone know a way I can do this?
> > 
> > Thanks
0
Utf
11/25/2009 4:42:02 PM
This does not solve the problem either

"David Biddulph" wrote:

> A number of options some of which *might* work:
> 1    Copy a blank cell, Select your data range, Edit/ Paste special/ Add
> 2    Select your column of data, then Data/ Text to Columns
> 
> Then format the cells appropriately.
> -- 
> David Biddulph
> 
> "Topher" <Topher@discussions.microsoft.com> wrote in message 
> news:83CC7FF1-01F4-4CDE-A2C4-99D2637697A4@microsoft.com...
> >I have a column of dates F6:F148, the dates have been entered in the
> > following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" 
> > &
> > "21 Jan 2009".
> >
> > I'd like to be able to change them all into a common date format 21 Jan 09
> > (I'll also change the sheet so that data is validated to this date format)
> >
> > Does anyone know a way I can do this?
> >
> > Thanks 
> 
> 
> .
> 
0
Utf
11/25/2009 4:42:06 PM
This technique has worked for me with data in the same format.

How does it not work for you?

(I'd try it again if I were you.)

Topher wrote:
> 
> I have a column of dates F6:F148, the dates have been entered in the
> following formats all entered as text "21.01.2009","21.1.2009","21 Jan 09" &
> "21 Jan 2009".
> 
> I'd like to be able to change them all into a common date format 21 Jan 09
> (I'll also change the sheet so that data is validated to this date format)
> 
> Does anyone know a way I can do this?
> 
> Thanks

-- 

Dave Peterson
0
Dave
11/25/2009 5:21:40 PM
That's too bad, Topher. When you are interested in solving your problem, 
post back with sufficient information. Like the data you have, the steps you 
followed, the results you got, and what you want instead. This problem has 
been solved many times before, and I'm sure it can be for you, if you are 
interested.

Regards,
Fred

"Topher" <Topher@discussions.microsoft.com> wrote in message 
news:6A93B698-7D08-4307-B386-7C56350C5563@microsoft.com...
> This does not solve the problem either
>
> "David Biddulph" wrote:
>
>> A number of options some of which *might* work:
>> 1    Copy a blank cell, Select your data range, Edit/ Paste special/ Add
>> 2    Select your column of data, then Data/ Text to Columns
>>
>> Then format the cells appropriately.
>> -- 
>> David Biddulph
>>
>> "Topher" <Topher@discussions.microsoft.com> wrote in message
>> news:83CC7FF1-01F4-4CDE-A2C4-99D2637697A4@microsoft.com...
>> >I have a column of dates F6:F148, the dates have been entered in the
>> > following formats all entered as text "21.01.2009","21.1.2009","21 Jan 
>> > 09"
>> > &
>> > "21 Jan 2009".
>> >
>> > I'd like to be able to change them all into a common date format 21 Jan 
>> > 09
>> > (I'll also change the sheet so that data is validated to this date 
>> > format)
>> >
>> > Does anyone know a way I can do this?
>> >
>> > Thanks
>>
>>
>> .
>> 

0
Fred
11/25/2009 7:59:13 PM
Reply:

Similar Artilces:

Cannot set task start or end date!!!
For some reason I can no longer set up task start or end dates. When I open the drop down list, it will not stay open when I release the left click. I can hold down left click and choose a date, but if I want to choose the "today" or "none" radio buttons, that will not work. Any ideas?? I am also using the Xobni program with Outlook, but do not have this problem with the same config. at home. I can set the status, priority and % complete fields with absolutely no problem. Thanks Howard Does it work in Outlook Safe Mode? Start-> Run; outlook.ex...

Text to columns issue
Some how I got set up so that when I paste text into an excel sheet it auto does the text to columns funtion. Each word is in its own cell. I would very much like to know how I got set up in this way and even more how I can stop it. I have opened new sheet and books and the same thing still happens so I think it must be some sort of global setting but I just can't find it. Thanks in advance. -- sminor ------------------------------------------------------------------------ sminor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37606 View this thread: http...

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

date format conversion
I have a date of 1/5/2006, it needs to be 01/05/2006. I ma having a hard time getting hte leading 0's where needed. Appreciate any help! -- Buck That sounds like a matter of FORMAT. The date itself, as 'numerical' value sounds fine. You can force a format (to display a STRING that represents a date, but *is* a string, not a date_time value) with the use of Format function: ? Format( #1/2/3# , "mm\/dd\/yyyy") 01/02/2003 Hoping it may help, Vanderghast, Access MVP "buckpeace" <buckpeace@discussions.microsoft.com> wrote in message news:01C568...

Import Text Wizard
I'm trying to open a .txt file from Windows that starts the Import Text Wizard automatically. All I've managed so far is to actually open it, yet if you open a text file in Excel it starts the wizard itself. Gary ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I believe that the Text Import Wizard runs automatically from the user interface. You can run it from Data, Text to Columns after the file is opened. -- Jim Rech Excel MVP ...

How do I wrap text for formulaes
I have a worksheet with a lot of formulaes - need to print on a single sheet but cannot seem to wrap text them - any ideas will be welcomed. TIA, Naz --- Message posted from http://www.ExcelForum.com/ I'm not sure if this works for formulas, but it certainly works for text. Highlight the cells you need to wrap, in the menu, click "format", "cells", click the "Alignment" tab, then put a checkmark in the box beside "Wrap text". If that doesn't work, someone else will have to come with a different fix. You might have to change your row h...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Send E-Mail using plain text only
I can find this OE feature anymore under WLM. Is it gone ? (Under Contacts) Thanks Stephan Tools/Options/Send - Choose either HTML or plain text. If no menu bar either Alt "M" or click on icon to the left of blue ? mark and select show menu bar "Stephan Koenig" <S.Koenig@LaserPlus.de> wrote in message news:OgKol3$zKHA.2552@TK2MSFTNGP04.phx.gbl... > I can find this OE feature anymore under WLM. Is it gone ? > > (Under Contacts) > > Thanks > > Stephan "Stephan Koenig" <S.Koenig@LaserPlus.de> wrote in...

XSLT Plain Text is Indented
Hi All, I'm having some trouble getting my XSLT to transform correctly. VS.NET addeds Indentations: <?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text" indent="no" /> <xsl:param name="Name"/> <xsl:template match="/"> Hello <xsl:value-of select="$Name" />, We have received your e-mail, and your question is in the process of being routed to the appropriat...

if meet critertia then countif(a1:a10, between dates)
I hv a table of info Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","2005/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 ...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201006/1 Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

OL2000 Text disappears for certain recipients
Hello, There's been a problem the last few days with the Outlook 2000 in the office I work. Certain users will reply to emails, but only most of the recipients will be to read the reply. Some just see the original message, with the reply text at the top completely gone. This is kind of nerve wrecking, since it's intermittent and not predictable. None of the Outlooks are formatted with rich text, and we use an ISP's email service. Any thoughts/suggestions would be greatly appreciated! Oh, and the OS's are either Win 2000 or XP. Thanks! ...

SQL server full text indexing, ranking and joining to other tables
i searched the net about sql server full text indexing and ranking and got this sample sql statement and it works using 1 table only: SELECT myTable1.id, myTable1.Title, fulltextSearch.Rank FROM myTable1 JOIN FreeTextTable(myTable1, [myField1], 'awesome ranking') fulltextSearch ON myTable1.id = fulltextSearch.[KEY] ORDER BY Rank DESC what i do not know is how to make it to work when it is joined to another table. how do i do that if i have these sample tables? myTable1 ---------- id myField1 1 rrrrrrr 2 qqqqqqq 3 kkkkkkk I don't see sample tables, merely one...

trying to return data with only the latest date
when i run this i get data for the last day. i'm trying to get it to return only for the latest date. for example the instance name column contains all the drive letters. i would like it to return one row for each machinename and drive letter. tried adding distinct but didn't work with free_hd_space_cte as ( select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, b.countervalue, convert(datetime, substring(b.counterdatetime,1, 16)) as TimeRead from counterdetails a inner join counterdata b on a.counterid = b.counter...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

How to automaticaly populate another date
I know how to do this in excel but can't figure it out in access. I want it so that when a users inputs data into one field the date (of when he put in the data) populates another field.Please helpThanks! Hi GregThe control that the user is entering data into has an AfterUpdate event which means that you can tell your system to do "something" after the field is updated.Open the form in design view and select the field that will have data entered into it.Right click and open the properties box.In the event column select AfterUpdateClic build option (...) and select codeYou will s...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

Summary Report
I have a summary report where I need a count of patients with their first visit date within a specific date range, but I also need a count of those same patients with one of four different outcomes during that same date range (delivered, miscarriage, transferred out, or no outcome withing the date range). Is this possible? Yes! Create a query that includes PatientID, FirstVisitDate and Outcome. Use the Between ... And construct in the FirstVisitDate field criteria. Click on the Sigma (looks like capital E) button in the menu at the top of the screen. Under PatientID, change ...

Need an Through/End date for the Historical Stock Status Report
Our customer wants to print a report for a prior month that shows the beginning quantity on hand for an item, all transactions for the month and the ending QTY balance. Put another way, the customer says "I want to know what inventory I started the month with, what happenned to it, and what I ended with." This could be accomplished by adding an end date option to the Historical Stock Status Report so we could run the As Of Date for the beginning of a prior month and the end date as the end of the prior month. ---------------- This post is a suggestion for Microsoft, and Micro...

change color in many text box
I have a form which takes information from a query. The information is orizontally aranged in the many text boxes . I would like to know how can i change the background color of the text box on the row by just doubleclicking on that particular row. Give me some suggestions please! ANY IDEEA, "florin" <florin_serban@hotmail.com> wrote in message news:ercv$ytcIHA.5164@TK2MSFTNGP03.phx.gbl... >I have a form which takes information from a query. > The information is orizontally aranged in the many text boxes . > I would like to know how can i change the background colo...

Adding text in Publisher before the actual html e-mail message?
I am trying to send out an e-mail made in Publisher. It's a newsletter, but in case some people can not view the actual html image, I want to have a link to a page on our website with the same content. The only way I have been able to add this text prior to the message is to send it to myself and then fwd it with the appropriate message about the link. "if you cannot see the following message, click here." Does anyone know a way to add this additional message from the Publisher document? I would greatly appreciate some advice. Hi Camil- If I understand you correctly, y...

If statements that look for a date range
Help!!! I am trying to look at an ActiveCell and determine if it is in a certain date range from the current date to 180 days prior and then complete an action. Any suggestions? Brian, You cannot look at the activecell with a formula as that would be circular. You could use this in say B1 =IF(AND(A1>=TODAY()-180,A1<=TODAY()), "do something", "do something else") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Brian Roberson" <Brian.Roberson@bellsouth.com>...

how do i zip an audio and text file together
I have an assignment for university. I have to record a short spoken text and separately answer written questions. The two files then must be zipped together before I can send them. I have windows 7 on a new computer and have no idea how to do this. I am still using an old laptop at present but get my wind. 7 next week. I do have acrobat on this old one. Thank you. Put them into a folder and compress the folder (right-click). On Nov 20, 6:31=A0am, Laneyb <Lan...@discussions.microsoft.com> wrote: > I have an assignment for university. I have to record a short spoken text...

Help needed on date matching and cell reference.
Hi, I need a formula to return the value of a cell from a nearby column, based on a matching of the months and years within a range of dates to the months and years within a given date. Here is the setup: Cell BA58 contains the given date. Column AP, starting in Cell AP59 and going down to AP2000 contains the array of dates that need to be evaluated to find which date matches the given date in Cell BA58 Column AR, starting in Cell AR59 and going down to AR2000 contains the array of numbers from which the result must be displayed. If a match is found between the given date in...