If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Text to Date
Change to appropriate cell reference:
=DATEVALUE(SUBSTITUTE(A1,"/",", ")) -- Best Regards, Luke M "PAL" wrote in message ... 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. |
#3
|
|||
|
|||
Text to Date
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" wrote in message ... 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. . |
#4
|
|||
|
|||
Text to Date
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" wrote in message ... 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. . |
#5
|
|||
|
|||
Text to Date
Using the menu commnd Text to Columns worked for me.
Select the range of "dates". Goto the menu DataText 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" wrote in message ... 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. |
#6
|
|||
|
|||
Text to Date
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" wrote in message ... 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. |
#7
|
|||
|
|||
Text to Date
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" wrote in message ... 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. . . |
#8
|
|||
|
|||
Text to Date
Hi
Did you not try Biff's suggestion of Datatext to ColumnsNextNextDateM/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" wrote in message ... 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. . . |
#9
|
|||
|
|||
Text to Date
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" wrote in message ... 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. . . |
Thread Tools | |
Display Modes | |
|
|