Text to Date

I have been given a database dump (thousands of rows) that put the dates in a 
text format like this:

Aug/11/2009

Is there any way to put this into a date format? Even if it means creating a 
new field breaking it up and doing some extra steps.

Thanks.
0
Utf
4/20/2010 7:06:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
1057 Views

Similar Articles

[PageSpeed] 43

Change to appropriate cell reference:
=DATEVALUE(SUBSTITUTE(A1,"/",", "))

-- 
Best Regards,

Luke M
"PAL" <PAL@discussions.microsoft.com> wrote in message 
news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
>I have been given a database dump (thousands of rows) that put the dates in 
>a
> text format like this:
>
> Aug/11/2009
>
> Is there any way to put this into a date format? Even if it means creating 
> a
> new field breaking it up and doing some extra steps.
>
> Thanks. 


0
Luke
4/20/2010 7:12:28 PM
Shouldn't this work also....

=IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))

"Luke M" wrote:

> Change to appropriate cell reference:
> =DATEVALUE(SUBSTITUTE(A1,"/",", "))
> 
> -- 
> Best Regards,
> 
> Luke M
> "PAL" <PAL@discussions.microsoft.com> wrote in message 
> news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
> >I have been given a database dump (thousands of rows) that put the dates in 
> >a
> > text format like this:
> >
> > Aug/11/2009
> >
> > Is there any way to put this into a date format? Even if it means creating 
> > a
> > new field breaking it up and doing some extra steps.
> >
> > Thanks. 
> 
> 
> .
> 
0
Utf
4/20/2010 7:57:01 PM
Yes, it should.  Are you saying it isn't?

PAL wrote:
> Shouldn't this work also....
> 
> =IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))
> 
> "Luke M" wrote:
> 
>> Change to appropriate cell reference:
>> =DATEVALUE(SUBSTITUTE(A1,"/",", "))
>>
>> -- 
>> Best Regards,
>>
>> Luke M
>> "PAL" <PAL@discussions.microsoft.com> wrote in message 
>> news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
>>> I have been given a database dump (thousands of rows) that put the dates in 
>>> a
>>> text format like this:
>>>
>>> Aug/11/2009
>>>
>>> Is there any way to put this into a date format? Even if it means creating 
>>> a
>>> new field breaking it up and doing some extra steps.
>>>
>>> Thanks. 
>>
>> .
>>
0
Glenn
4/20/2010 8:05:15 PM
Using the menu commnd Text to Columns worked for me.

Select the range of "dates".
Goto the menu Data>Text to Columns
Click Next twice
In Step 3 of the wizard select Date and from the drop down select MDY
Click Finish

Then format in the date style of your choice.

-- 
Biff
Microsoft Excel MVP


"PAL" <PAL@discussions.microsoft.com> wrote in message 
news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
>I have been given a database dump (thousands of rows) that put the dates in 
>a
> text format like this:
>
> Aug/11/2009
>
> Is there any way to put this into a date format? Even if it means creating 
> a
> new field breaking it up and doing some extra steps.
>
> Thanks. 


0
T
4/20/2010 8:50:28 PM
You can also select all the dates, click Edit/Replace on the menu bar, put a 
"/" (without the quote marks) in the "Find what" field and ", " (comma 
space, again, without the quote marks) in the "Replace with" field and 
finish off by clicking the "Replace All" button.

-- 
Rick (MVP - Excel)



"PAL" <PAL@discussions.microsoft.com> wrote in message 
news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
> I have been given a database dump (thousands of rows) that put the dates 
> in a
> text format like this:
>
> Aug/11/2009
>
> Is there any way to put this into a date format? Even if it means creating 
> a
> new field breaking it up and doing some extra steps.
>
> Thanks. 

0
Rick
4/21/2010 2:40:10 AM
Correct.  It is not.  I get the pesky, #Value!.  Perhaps ISERROR would work, 
but it isn't either.

"Glenn" wrote:

> Yes, it should.  Are you saying it isn't?
> 
> PAL wrote:
> > Shouldn't this work also....
> > 
> > =IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))
> > 
> > "Luke M" wrote:
> > 
> >> Change to appropriate cell reference:
> >> =DATEVALUE(SUBSTITUTE(A1,"/",", "))
> >>
> >> -- 
> >> Best Regards,
> >>
> >> Luke M
> >> "PAL" <PAL@discussions.microsoft.com> wrote in message 
> >> news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
> >>> I have been given a database dump (thousands of rows) that put the dates in 
> >>> a
> >>> text format like this:
> >>>
> >>> Aug/11/2009
> >>>
> >>> Is there any way to put this into a date format? Even if it means creating 
> >>> a
> >>> new field breaking it up and doing some extra steps.
> >>>
> >>> Thanks. 
> >>
> >> .
> >>
> .
> 
0
Utf
4/21/2010 11:56:01 AM
Hi

Did you not try Biff's suggestion of
Data>text to Columns>Next>Next>Date>M/D/Y

That works perfectly and is the easiest way to go IMO.
--
Regards
Roger Govier

PAL wrote:
> Correct.  It is not.  I get the pesky, #Value!.  Perhaps ISERROR would work, 
> but it isn't either.
> 
> "Glenn" wrote:
> 
>> Yes, it should.  Are you saying it isn't?
>>
>> PAL wrote:
>>> Shouldn't this work also....
>>>
>>> =IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))
>>>
>>> "Luke M" wrote:
>>>
>>>> Change to appropriate cell reference:
>>>> =DATEVALUE(SUBSTITUTE(A1,"/",", "))
>>>>
>>>> -- 
>>>> Best Regards,
>>>>
>>>> Luke M
>>>> "PAL" <PAL@discussions.microsoft.com> wrote in message 
>>>> news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
>>>>> I have been given a database dump (thousands of rows) that put the dates in 
>>>>> a
>>>>> text format like this:
>>>>>
>>>>> Aug/11/2009
>>>>>
>>>>> Is there any way to put this into a date format? Even if it means creating 
>>>>> a
>>>>> new field breaking it up and doing some extra steps.
>>>>>
>>>>> Thanks. 
>>>> .
>>>>
>> .
>>
0
Roger
4/21/2010 12:10:26 PM
The supplied formula works perfectly with the data you provided below 
("Aug/11/2009").  Exactly what input and formula is giving you a #Value! result?

Keep this information from the help file in mind:


Syntax

DATEVALUE(date_text)

Using the default date system in Excel for Windows, date_text must represent a 
date from January 1, 1900, to December 31, 9999.  DATEVALUE returns the #VALUE! 
error value if date_text is out of this range.


PAL wrote:
> Correct.  It is not.  I get the pesky, #Value!.  Perhaps ISERROR would work, 
> but it isn't either.
> 
> "Glenn" wrote:
> 
>> Yes, it should.  Are you saying it isn't?
>>
>> PAL wrote:
>>> Shouldn't this work also....
>>>
>>> =IF(R2="","",(DATEVALUE(SUBSTITUTE(R2,"/",", "))))
>>>
>>> "Luke M" wrote:
>>>
>>>> Change to appropriate cell reference:
>>>> =DATEVALUE(SUBSTITUTE(A1,"/",", "))
>>>>
>>>> -- 
>>>> Best Regards,
>>>>
>>>> Luke M
>>>> "PAL" <PAL@discussions.microsoft.com> wrote in message 
>>>> news:2E5E64F4-8B6A-44D5-95C9-8FD45A326DB3@microsoft.com...
>>>>> I have been given a database dump (thousands of rows) that put the dates in 
>>>>> a
>>>>> text format like this:
>>>>>
>>>>> Aug/11/2009
>>>>>
>>>>> Is there any way to put this into a date format? Even if it means creating 
>>>>> a
>>>>> new field breaking it up and doing some extra steps.
>>>>>
>>>>> Thanks. 
>>>> .
>>>>
>> .
>>
0
Glenn
4/21/2010 2:18:00 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

if statement with text
I am trying to create an if statement where the string of text contains total, if yes then perform calculation base on that row and column. if no total in text leave blank help =IF(ISERROR(FIND("Total", A1)),"",-yourCalculation-) FIND returns #VALUE if "Total" is not in A1 Therefore, ISERROR returns False if "Total" is in A1 (i.e., it was found, no error) HTH, -- George Nicholson Remove 'Junk' from return address. "jerry" <jerry@discussions.microsoft.com> wrote in message news:E249057A-F6E4-45BF-929A-B2BB61C3A700@micro...

Compare Now() to a European date
This is driving me nuts, I have a list of certificates. In column B their expiry dates are entered as Europeans, some at least, do. Like today would be 20080524. I want these cells to change colour with conditional formating. For instance becoming yellow when there is less than three months between now and the expiry date, and then becoming red when there is less than one month to expiry. Else they should remain without colour. I have read through a hundred posts dealing with similar needs and seemingly fine replies, but I get nowhere with my particular sheet. When I format my B cell as ...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

convert text in excel to uppercase
Is it possible to convert all text in a workbook or on a spreadsheet to all uppercase? In VBA Editor Sub MakeUpper() Dim MySht As Worksheet, MyCell As Range For Each MySht In ThisWorkbook.Sheets For Each MyCell In MySht.UsedRange.Cells MyCell = UCase(MyCell) Next Next End Sub "elaine" wrote: > Is it possible to convert all text in a workbook or on a spreadsheet to all > uppercase? Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany "elaine" <elaine@discussions.microsoft.com> s...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I have in the database, displays the current date when the program is openend. I thought this would have been easy, but obviously not! Thanks for any help. CEL504 wrote: > Could anyone tell me how to set the properties so that the ActiveX calendar I > have in the database, displays the current date when the program is openend. > > I thought this would have been easy, but obviously not! > > Thanks for any help. http://groups.google.com/group/comp.databases.ms-access/msg/1564d683ede98f8c Jame...

Getting right date value
I setup my DTPicker control to be used only as a date control, yet I'm noticing that sometimes it will give back a date AND a time all in the same "value" variable. Since it appears that a variable of type "Date" can give back both a date and time, how can I eliminate the time half of a date value??? I might not be able to exactly control the DTPicker control to give me JUST a date, so I'm just curious what to do if it gives me back both a date & time. thank u Hi, Try this : Dim x as date x = cdate(clng(DTPicker1.value)) &qu...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...

Halfway through my publisher document the text starts scrambling
I am in the middle of a Publisher document that I have to get out ASAP and the words are scrmbling all of a sudden; for instance instead of the it prints out eht. HELP!! Might try an updated printer driver. What version Publisher are you using? What printer? What version Windows? Canon has issues with Publisher 98 and Windows 98 How to troubleshoot text printing (inkjet printers) in Publisher 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;198258&Product=pub PUB2000: Troubleshooting Text Printing (Laser Printers) http://support.microsoft.com/default.aspx?scid=kb;en-us;198...

I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. ...

Find a text
Hi all I am trying to find a text and delate a Row if the text is in the row. But I am not sure how to do it. Can someone help me? Option Explicit Sub FindText() Dim Cell As Range For Each Cell In ActiveSheet.UsedRange If Cell = "Samtals hreyfing:" Then ‚the text Samtals hreyfing sin in in the column E:E ‚If the text Samtals hreyfing: is in the row then I want to delete the Row End If Next Cell End Sub I think you may be looking for this... Sub FindText() Dim Cell As Range For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E")) ...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...

Help with formula: finding text
Hi, I would like to ask for help with a formula for comparing name in a cell with a list of names in a table. If there is a match it should return a associated text to the matched name from the table. If no match it should just leave the cell blank. Thanks in advance Jonas Hi =VLOOKUP(A1,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "Jonas Ornborg" wrote: > Hi, > I would like to ask for help with a formula for comparing name in a cell > with a list of names in a table. If there is a match...

Propose a meeting with multiple dates in Outlook
How do you propose a meeting with multiple dates? Recurrence button S wrote: > How do you propose a meeting with multiple dates? > "S" <S@discussions.microsoft.com> wrote in message news:1448A718-D76A-4874-9FC0-03AFD4367566@microsoft.com... > How do you propose a meeting with multiple dates? See this: http://www.slipstick.com/calendar/pickmeeting.htm -- Brian Tillman [MVP-Outlook] On 3/11/2010 7:16 AM, S wrote: > How do you propose a meeting with multiple dates? > A third-party solution called Tungle works nicely for this. ht...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...

Null date parameter
Hi How can I pass a null value to a date parameter in a Sub? Thanks Regards hi John, John wrote: > How can I pass a null value to a date parameter in a Sub? you have to pass it as Varian as normal data types cannot be Null: Public Sub yourSub(ADateNullable As Variant) On Local Error GoTo LocalError Dim DateValue As Date If IsNull(ADateNullable) Then Else DateValue = CDate(ADateNullable) End IF Exit Sub LocalError: If Err.Number = 13 Then ' Type mismatch. End If End Sub mfG --> stefan <-- ...

Serial Date in Multiple Worksheet
Hi, I need to create a workbook with the same Multiple Worksheet (TAB) form on it. This form will be used for each day of the month. The problem is I want to be able to enter the date on the first TAB and has the date replica to each worksheet add 1 day to each sheet. Example: The first sheet - Monday, December 26, 2005; the second - Tuesday, December 27, 2005; the third - Wednesday, December 28, 2005; and so on. Thanks in advanced. Try running the sub below (Sub is by Dave Peterson, and was plucked from a post by Gord Dibben in .newusers) Here's how to set it up .. In a new b...

Date & Time
Hi, I have a form called frmDetails and I have set a text box (txtDate) in the form with the control source of =Format(Now(),"dddd"", ""mmm d yyyy"", ""hh:nn:ss ampm") but the problem is I want this Time & Date to be recorded in the table (called tblDetails) as it needs to be used for referancing when entries were entered into the database. Can anyone tell me how to do this. Thanks Fiona Control source should be the table column. Make the default value your Now() etc. -Dorian "fiona.innes@metacor.co.uk" wrote: > Hi, &g...