Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Need to convert a bunch of text files that include dates in the "Fri Aug 22
13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Well, this is a bit clumbersome, but it does the job.
First, create a VLOOKUP table...I used J1:k12, with the three letter months down column J and the corresponding month number down column K. Then, assuming your string is in A1, put this in B1 and copy down........... =DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3),$J$1:$K$12,2 ,FALSE),MID(A1,9,2)) hth Vaya con Dios, Chuck, CABGx3 "Dan Harlan" wrote: Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Using Data|Text to Column, I got this in columns A thru F
Fri Aug 22 13:49:20 EDT 2003 I believe you also have this In G1 I entered the formula =DATE(F1,MATCH(B1,{"Jan";"Feb";"Mar";"Apr";"May";" Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),C1)+D 1 which gave me 22/08/2003 13:49 (I use Canadian date format; you will get month & day in US format) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Dan Harlan" <Dan wrote in message ... Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Dan,
If you're opening, changing, and saving the files by hand, change the extensions of the input files to .txt. Then use File - Open. It will start the Text Import Wizard, where you can specify Delimited, The tab delimiter, and other stuff. Then change the formatting (Format - Cells - Number) of the column with your dates/times. Then save the file, setting the file type as *.txt or *.csv. The import, since it's tab-delimited, will not parse that unusual date field. But you can do that with the column that it winds up in with formulas in other columns. If your "Fri Aug 22 13:49:20 EDT 2003" is in column B, you could use =DATEVALUE(MID(B2,5,6)& ", " & RIGHT(B2,4)) The day (22) must always be 2 characters for this to work properly. It might need some tweaking. Format the cell containing the formula as a date (Format - Cells - Number - Date). If the layout of all the text files is the same, you'll save time by setting up an Import Query (Data - Import external data - Import Data). That will import the various files into the sheet containing the formula that builds a new date, which you can copy/paste using paste special values, from the formula cell to where you want it. Or you could use Data - Text to columns on that column alone using space as the delimiter. Then go after the parts you want. This sounds like macro time, once you find a good algorithm, since you have many to do. -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Dan Harlan" <Dan wrote in message ... Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
=--(MID(A1,9,2)&"/"&MID(A1,5,3)&"/"&RIGHT(A1,4)&" "&MID(A1,11,9)) and format
appropriately -- David Biddulph "Dan Harlan" <Dan wrote in message ... Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Need to convert a bunch of text files that include dates in the "Fri Aug
22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan If you are up for a VBA macro solution, then give this subroutine a try... Sub FixDateTime() Dim R As Range Dim SelectedCells As Range Dim Parts() As String Const DatePattern = "[SMTWF][uoehra][neduit] [JFMASOND][aepuco]" & _ "[nbrylgptvc] [ 0123]# ##:##:## [ECMP][SD]T ####" If Selection.Count = 1 Then Set SelectedCells = ActiveCell Else Set SelectedCells = Selection End If For Each R In SelectedCells If R.Value Like DatePattern Then Parts = Split(R.Value, " ") R.Value = CDate(Parts(2) & " " & Parts(1) & " " & _ Parts(5)) + CDate(Parts(3)) End If Next End Sub Simply select the cells you want to change and run the macro. Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
Thansk everyone. I used Earls solution as it was the shortest. Really
appreciate everyone's quick help. "Dan Harlan" wrote: Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to convert date from "Fri Aug 22 13:49:20 EDT 2003"
If you go down the text to columns route you can probably skip the MATCH()
function and get away with =--(C1&"-"&B1&"-"&F1)+D1 or =--(C1&"/"&B1&"/"&F1)+D1 or =DATEVALUE(C1&"/"&B1&"/"&F1)+D1 or ... -- David Biddulph "Bernard Liengme" wrote in message ... Using Data|Text to Column, I got this in columns A thru F Fri Aug 22 13:49:20 EDT 2003 I believe you also have this In G1 I entered the formula =DATE(F1,MATCH(B1,{"Jan";"Feb";"Mar";"Apr";"May";" Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),C1)+D 1 which gave me 22/08/2003 13:49 (I use Canadian date format; you will get month & day in US format) "Dan Harlan" <Dan wrote in message ... Need to convert a bunch of text files that include dates in the "Fri Aug 22 13:49:20 EDT 2003" format. Have been unable to break them into separate columns during import as the other columns are all tab delimited. Wish to convert this into a standard date/time format. How best to achieve this as I have numerous files to do this with. TIA, ~Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel 2003 is there a way to prevent "Save As" and "Print"? | Excel Discussion (Misc queries) | |||
Convert text "date" (DDMMMYY) to datetime value of YYYY-MM-DD - ho | Excel Discussion (Misc queries) | |||
Is there a "last saved on date/user" macro/function for Excel 2003 | Excel Discussion (Misc queries) | |||
convert "15000" to "Fifteen thousand" in excel cell? | Excel Worksheet Functions | |||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions | Excel Worksheet Functions |