Convert imported text to date time

I've read all the posts on this but I can't seem to find one that gives me 
the answer with a time field.  I am importing from Excel as a .cvs and the 
date time comes in as a text field with 12/09/2009 0830.

I need to convert this from text to an actual date/time.  mm/dd/yyyy hh:mm
preferably in military time.  I need to calculate the minutes from Actual 
Departure time to Actual Arrival time.  You guys will forever be my hero if 
you can give me the directions on doing this.  Thanks!

Cindy
0
Utf
2/5/2010 3:02:01 AM
access 16762 articles. 3 followers. Follow

2 Replies
1107 Views

Similar Articles

[PageSpeed] 13

I pieced this together rapidly ad it seems to work.

Function ConvDT(sFullDT)
   SD = Left(sFullDT, InStr(sFullDT, " ") - 1)
   sT = Right(sFullDT, Len(sFullDT) - Len(SD) - 1)
   sT = Left(sT, 2) & ":" & Right(sT, 2)
   p = SD & " " & sT
   ConvDT = Format(p, "yyyy-mmm-dd hh:nn")
End Function

you'd use it like:
datediff("n", Actual Departure time, Actual Arrival time)
datediff("n",ConvDT("12/09/2009 0830"),ConvDT("12/09/2009 1400"))

Need to add error handling and dim declarations... but it does work.
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Cindy" wrote:

> I've read all the posts on this but I can't seem to find one that gives me 
> the answer with a time field.  I am importing from Excel as a .cvs and the 
> date time comes in as a text field with 12/09/2009 0830.
> 
> I need to convert this from text to an actual date/time.  mm/dd/yyyy hh:mm
> preferably in military time.  I need to calculate the minutes from Actual 
> Departure time to Actual Arrival time.  You guys will forever be my hero if 
> you can give me the directions on doing this.  Thanks!
> 
> Cindy
0
Utf
2/5/2010 3:42:01 AM
On Thu, 4 Feb 2010 19:02:01 -0800, Cindy <Cindy@discussions.microsoft.com>
wrote:

>I've read all the posts on this but I can't seem to find one that gives me 
>the answer with a time field.  I am importing from Excel as a .cvs and the 
>date time comes in as a text field with 12/09/2009 0830.
>
>I need to convert this from text to an actual date/time.  mm/dd/yyyy hh:mm
>preferably in military time.  I need to calculate the minutes from Actual 
>Departure time to Actual Arrival time.  You guys will forever be my hero if 
>you can give me the directions on doing this.  Thanks!
>
>Cindy

Easily done: 

CDate(Format([yourfield], "@@@@@@@@@@@@@:@@"))

Or,

CDate(Left([yourfield], InStr([yourfield], " ") - 1) +
CDate(Format(Right([yourfield], 4), "@@:@@")


You could then use the DateDiff() function (see the VBA help) to calculate the
elapsed time.
-- 

             John W. Vinson [MVP]

0
John
2/5/2010 3:45:11 AM
Reply:

Similar Artilces:

Post Dated Cheque in Receivables
Hi to All GP Tech, Here i have some confusion in Receivable/payable Module GP-8.0 SP-3,having complete Module sales-Receivable,SalesOrder Processing,Purchase&Payable,Inve ntory & Finance. 1-When preparing A Cash Reciepts for normal cash cheque or cash its straight prepare print and post , but what happen when postdated cheque comes as 12dec2006, $50.00, from Customer 'ABC' what should i do , how system and posting take care on the cheq realizeation date and how accounts GL side and as well as customer side will behave - is their any way it can be record , their is no p...

"Ignore original message text" not working with Word as editor on one machine?
I know people have run into this, but has anyone found a solution? I've got one machine that refuses to "Ignore original message text" when I use Word as the Editor. If I just go back, and uncheck use Word as the editor, it works fine (so no, reply ticks are not turned on). On another machine, also with Office XP (but with Windows XP versus Win 2000), it works just fine. Has anyone come across a fix? ...

Convert Numeric field to Date
Hello, I've seen similar questions to this but need some help/clarification. I have Access 2003 with linked tables to a DB2 database. The fields I am querying are numeric in this format 20070810 (yyyymmdd) What I am trying to do is display customer request date and actual ship date in mm/dd/yyyy format on one query. On another query I am trying to summarize item shipments by month. I know next to nothing about this. Any help with baby steps would be greatly appreciated. Probably easiest to convert the numeric value to a string, then convert to a date format for display. You say yo...

Date/Time criteria problem
I am trying to create a query that allows a user to select a specific date for which to show results. Following is sample SQL with a fixed criteria: SELECT qryShopJobsIO1.JOB_NO, qryShopJobsIO1.[TOTAL QUANTITY ORDERED], qryShopJobsIO1.[ENTRY DATE], qryShopJobsIO1.SumOfTotalHours FROM qryShopJobsIO1 WHERE (((qryShopJobsIO1.[ENTRY DATE])=#4/27/2010#)); This particular example returns no records. I know that I have records for which the ENTRY DATE field is 4/27/2010. I believe that the problem is that the default value of that field (in the table definition) is set to =Now() ...

Problem with date
Hi, I just converted my adp project from Access 2000 to Access 2003. In the 2000 version the date written on the SQL server are OK (mm/dd/yyyy). I use the Access 2000 in French and the SQL server is version 7 in English, and the date is ok on the SQL server. After the conversion the date on the SQL server is dd/mm/yyyy. I used Access 2003 in French and the same sql server that I used with the 2000 version. The regional paramater are the same, English (USA), but my workstation is a Windows French 2000 . The command that insert the data in the sql server is an INSERT INTO with a variable, ...

importing a website to fp2003
sorry for the second posting of this, but i think this might be the better place for it: i am trying to import a couple of sites into frontpage for updating - for the first time. i from the drop down menu, i choose import and the import box opens. i click the 'from site' button and the import wizard box opens. i choose the 'http" radio button and type in the address, then click 'next.' for a moment a search box opens and then closes, returning me to the import wizard. it now asks me to specify where i would like to create a local copy (i choose 'my web s...

Time Zone
What formula I can use to make the changes of hour between cities or continents? What formula I can use to make the changes of hour between cities or continents? -- HR157 ------------------------------------------------------------------------ HR157's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34458 View this thread: http://www.excelforum.com/showthread.php?threadid=544337 =A1+(1/24*3) Vaya con Dios, Chuck, CABGx3 "HR157" wrote: > > What formula I can use to make the changes of hour between cities or > continents? > > Wh...

spreadsheet with several row, looking to view one at a time while.
I have 193 rows on my spreadsheet, with the top 2 rows frozen to view as I scroll down the list of rows. I would like to bring the rows to meet the frozen information but it is only letting me see every 3rd row instead of each row one at a time. Thanks How are you scrolling? If I scroll with my mouse wheel I get what you describe, but if I scroll with the right scroll bar/arrows I get one row at a time. HTH Otto "Angie" <Angie@discussions.microsoft.com> wrote in message news:214FF948-D453-4945-9E47-756A86717ABD@microsoft.com... >I have 193 rows on my spreadsheet, w...

How to have a shape text block that doesn't wrap the text.
I'm sure this is basic, but can't figure it out. Am creating Tivoli shapes, and would like to put a text block at the bottom of the shape that doesn't overlay the icon itself. It seems to want to keep the text block the size of the shape, and overlay the icon with the text only. Any clues? In the TxtWidth cell, enter this formula: =TEXTWIDTH(TheText) -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Sluggo59" <Sluggo59@discussions.microsoft.com> wrote in message news:C5296795-E51F...

SUMPRODUCT using Time between two values
Thanks in advance for any help you can provide me. I have a sheet with the following layout of data (shortened here and changed for obvious reasons) Venue Date Event Start Event End Act Disney 4/13/10 7:00 PM 10:00 PM Goofy MSG 2/13/10 1:00 PM 3:00 PM Basketball Park 3/14/10 6:00 PM 8:00 PM Harlem Globetrotters So, what I have is a user input a Venue, Date, Time Value, and I would need the Act returned... For example, Disney, 4/13/10, 8:25:13 PM, would ou...

Converting access tables to XML for use in .NET web app
I need to access tables in an .mdb file and convert them to XML for use in my ..NET 2.0 web application. Can anyone point me to any tutorials or references on how to accomplish this? (Note: I am using VB) Thanks! ...

Format text not all caps
I am doing a mail merge from an xls file into word. The excel file that I purchased is in all caps, ie JOHN SMITH. I would like to change the xls file to be John Smith. I see from excel Help something called Proper, but it doesn't tell me how to apply it to columns or cells. How do I do this??? I have no problme actually performing the mail merge. The text needs to be changed within excel so as not to change what I have in the word source doc .. Thanks from Southern California. hi Justin if your data is stored in column A put the following formula in B1 =PROPER(A1) copy this formu...

Fitting Text in Quadrants
How can I setup text to fit in 4 quadrants on the same page? Desired Result: A | A A | A I want to have the same text fit in each quadrant and print the result on one 8 1/2 X 11 sheet of paper. These quadrants represent postcards. Thannks in advance. Page setup, postcards... In Publisher 2000 and below, page setup, special size, postcard -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "razornt" <razornt@discussions.microsoft.com> wrote in message news:E716FC7A-CD8B-4103-B526-187E57DE0C03@microsoft.com... >...

Converting Text months to sortable Numbers or Dates
I have a column with months as text (April, etc.). Trying to sort them in order, is there a way to convert them to dates or numbers in order to sort Hi Greg- No need to convert... Click in that column, then go to Data>Sort & click the Options button. Open the 'First key sort order' list and choose appropriately, the click OK in each box. HTH |:>) "Greg" wrote: > I have a column with months as text (April, etc.). Trying to sort them in > order, is there a way to convert them to dates or numbers in order to sort That works well when I have the months...

Importing QIF files in Money 2004 produces duplicate transactions
Hi. After importing all my accounts into Money 2004 from QIF files, trnasfers between accounts generate duplicated records in related accounts. How to fix this problem? In microsoft.public.money, Adrian Guinsburg wrote: >Hi. After importing all my accounts into Money 2004 from QIF files, trnasfers >between accounts generate duplicated records in related accounts. >How to fix this problem? Restore your file to the way it was before import. Then import again. This time import all QIF files at once using Ctrl+<click> to select the QIF files. During the File->Import process...

Text Formatting 2
I have a spreadsheet with 2 versions of text in 1 columm 1. 1 column = firstname and initial. For example Dave C. 2. 1 column = firstname. For example Dave DaveO kindly provided this suggestion: =MID(D7,1,FIND(" ",D7,1)-1) where D7 contains the name in question. This formula returns everything before the first blank space in the entry This works perfectly for situation number 1, but in situation 2 I get a #VALUE! error. How can I mask the cell to be just the first name in both scenarios? Thanks Ben Hi Ben One way =IF(ISERROR(FIND(" ",D7,1)),D7,MID(D7,1,FIND(&...

How do I find days between two dates?
I'm using this formula =TODAY()-(7/1/2004) to find the days between today (4/2) and 7/1. The result is 38079.99651. Why doesn't this give the days? Also, is there a way to display the number of months and days in the same cell? For example =MONTH(TODAY())-MONTH(7/1/2004) with the day formula and have the result read "2 months 28 days" or so? Thanks, Brett Try: =today()-date(2004,1,7) date() is y,m,d--did you mean July 1, 2004 or January 7, 2004? And format the cell as General. 7/1/2004 looks like arithmetic (7 divided by 1 divided by 2004). And when you format a da...

Time mail was opened
Is is possible to tell on exchange server 2000 or through outlook what time an email was first opened? The client is using outlook 2003. I've been searching for several hours and haven't come up with any mention if this is even possible. Can anyone answer this or point me in the right direction? tia, Kevin Here is a close resemblance. First go to the message tracking tool in Exchange System Manager and checked when it was delivered into the (local) user's mailbox. Second go to the Information Store where that user's mail box is and see when was the last time the user lo...

HTML/Rich Text Messages: Always Re-Downloading Bodies--Why...?
Currently using Outlook 2007, but has been the case with previous versions as well. Once a text with pictures, formatted text, etc (HTML and rich-text) downloads I view it -- but the message re-downloads every time I click on it in my inbox. I've checked the settings, but can't locate anything that'll make the message download, and stay downloaded, until I delete. Outlook isn't all that fast to begin with, but having to redownload a message because I've clicked it's header slows things down even more. ...

Converting YYYYMMDD Special Format to Text
What formula or function converts a cell with a special date format of YYYYMMDD, lets say 20030801, to the text string "20030801"? In my case the special format option YYYYMMDD makes the number appear as 20030801 but will only display the actual date value of 37834 when attempting to convert the date to a text string. One way: =TEXT(A1, "yyyymmdd") where A1 contains your value. In article <108901c38960$b4c9b6d0$a301280a@phx.gbl>, "Mike" <mbredal@tetontel.com> wrote: > What formula or function converts a cell with a special > ...

CRM 4.0 customize Activity Created Date
cannot select date and time, format is greyed out and displays date only. How can I get it to take both? I've even tried exporting to XML, and re-importing but to no success. Hi, Can you please elaborate on you questions, are you trying to set value to the Created On Date on the activity? If you, you can't set the created on time, this is a system field set by the CRM system. Darren Liu,Microsoft CRM MVP Crowe Chizek and Company http://www.crowecrm.com On May 22, 10:15 am, Magic23 <carlroyk...@gmail.com> wrote: > cannot select date and time, format is greyed out ...

Replace text in field code
I have some REF fields that point to bookmarks for document ranges that are deleted in my VBA code. I want the REF field to point to a new bookmark. I want to use something like the following myRange.fields("REF OQTempRHMappingSection \r \h").code = replace(myRange.fields("REF OQTempRHMappingSection \r \h").code, "TempRH", "TempOnly") Of course, this does not work or I wouldn't be writing this question. Is there a way to accomplish what I'm trying to do? If I reveal codes in the document, I can perform the edit manually. I think...

Imported statements in Money 2008 disappear
I have downloaded statements from credit union in 'qfx" format. Using 'Money 2008 Home and Business' the "file' 'import' says the file import complete but does not ask which account should be associated with the statement and the statement seems to be nowhere in Money. Does anyone else have this problem? Where is the imported file? Thank you I had something similar with a recent .ofx statement. Found it in Temporary Internet Files folder. On my WindowsMe system Temporary Internet Files is located C:\WINDOWS I moved the statement to my desktop. ...

I could really need your help on importing contacts into Outlook (Please?)
Hi all, I need to import literally 3000 contacts into Outlook. Unfortunately, the list (in plain text) looks like the list on the bottom of my message (small example). I'm faced So, clearly, the data is not in a databaserecordformat (with fields from the left to the right). Thus, I can not assign headers to it (in Outlook), and thus I can not map the fields during the import into Outlook. I know I can use "transpose" in Excel to change rows into columns, but this still requires a lot of manual work: I have to select each block, Edit/Copy, Edit/Paste Special/Transpose. With 3000 ...

copy and paste text crashes word
If I copy text from Textedit or a pdf (using Preview) and then paste that text into Word it'll crash. Once that happens I have to log out of OSX and come back before Word will run without crashing (it'll open and as soon as I enter a keystroke or select a menu it crashes). Copy and pasting from within word is fine. Running the latest version of Office 2008. Any thoughts? What version/update of OS X? Have you recently repaired disk permissions? The behavior you describe suggests that it may well not be a fault of Word, but that perhaps something is being put on the clipbo...