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. 2 followers. Follow

2 Replies
971 Views

Similar Articles

[PageSpeed] 56

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:

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Odd date problem
I'm reposting this as this is a very strange problem in Access. I'm using 2003. After my entry is an entry by missinglinq via AccessMonster.com who was able to reproduce the error. Looking forward to comments/fixes. Thanks, Rob I have the following in the DblClick event for the control CaseStart which is a date/time field. The problem is that once the control is double clicked the form remains in edit mode, even though I've told it not be editable. Additionally, when the control is double clicked, it shoudn't enter in the time until the Edit button has been clicked o...

Is there a registry key to specify the idle time before tracking info is processed?
Hello, In Outlook, read receipts and delivery notifications are processed in idle time, which seems to be about 1-2 minutes. Anyone know if a registry key specifies the exact idle time? We're looking to reduce it, like to zero if feasible! Thanks, - Alan. How did you come to this conclusion? Receipts are processed immediately = when using Exchange. Perhaps you need to specify your environment. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, Alan asked: | Hello, |=20 | In Outlook, read receipts and de...

See that important patch from the MS Corp.
--mnqbifzstjwnoqccp Content-Type: multipart/related; boundary="rbkkbifudezkn"; type="multipart/alternative" --rbkkbifudezkn Content-Type: multipart/alternative; boundary="vdsdrqruhwoiboko" --vdsdrqruhwoiboko Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Ins...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Some wat to change Outlook's importing rules for contacts?
When I import files to Outlook's contacts, the "File As" seems to default to the Name. This is fine when there is no Company, but when there is a Name and a Company I'd rather have the "File As" default to the Company. Is there any way to make this happen without (as I have been doing) manually going through the contacts and editing them? ...

how to convert excel's .cvf file to .csv file
...

diagonal text
HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE On Sun, 30 Oct 2005 11:06:07 +0100, SCOTT wrote (in article <8844C60C-FD08-4FB5-B289-D530CAA285DA@microsoft.com>): > HOW DO I MAKE MY TEXT GO IN A DIAGONAL WAY ACROSS THE PAGE Lose the caps. It's considered yelling, is incredibly rude and makes your posts very hard to read. Please explain what it is you're attempting to accomplish and your Publisher version. By the way, questions end with question marks (?). What version of Publisher are you using? In 2000, arrange, rotate or flip, custom rotate, input your n...

Weird import error
I often import files that have .com & .org in addresses in the text. For some reason, the import usually drops the last "m" or "g" in the import process. Any idea why? It's been going on for quite a while, at least since Pub 2003, I just never got around to trying to figure out or ask why it would do that! I just circle it during proofreading (which I do on paper) & change it when I tune up the publication before finalizing it. Thanks. bj ...

Importing Multiple contacts to a master account
I'm trying to get the new microsoft CRM to group multiple contacts under 1 key account. For example: I have 10 contacts for Nokia and I want to put them all under Nokia. Instead, when I export my excel file to the crm it turns all the contacts into there own master files, unconnected. Is there a way to import the files and have them sync up under the proper master account? MS has provided a data migration framework that you can use to do this. "Brian B." <bbrunson@gpcreative.com> wrote in message news:0a7501c34c6f$2644cdb0$a501280a@phx.gbl... I'm trying to ge...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

change case of text #2
Some recent data was imported to an Excell worksheet that had a large amount of text in capital letters. Is there a way to change this data to "PROPER" throughout the entire worksheet? Thank you Gloria Anderson at Habitat for Humanity of Key West and the Lower Florida Keys =proper(range) will do the trick Mike "habikeys" <anonymous@discussions.microsoft.com> wrote in message news:017801c39f21$a016ece0$a501280a@phx.gbl... > Some recent data was imported to an Excell worksheet that > had a large amount of text in capital letters. Is there a > way to c...

Outbox displays Received Date
Need some help on this one. I'm using Outlook 2000 on a Windows XP machine and for some odd reason the Outbox now displays the "Received" date and the Inbox displays the "Sent" date. How do i correct this. It seems to be hidden in the PST file because when I login on a different machine it shows the same thing. Right click on the column headers and choose the "Field Chooser". Drag the field you want to see from the Field Chooser into the column headers area. Drag column headers you don't want to see away from the column headers. "Lee" ...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Add help text to a form field?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I'd like to give people instructions about how to fill in form fields in the form itself. I tried &quot;adding help text,&quot; but nothing shows up in the form field. Is what I am trying to do possible, and, if so, what am I doing wrong? What you're using appears in the Status Bar when the field is accessed. Unless people know to look there it does practically no good. IMHO it's more effective to include instructional text as default content for the field or provide explicit labeling in an adjacent table c...

inserting current time absolute value
hey everyone I'm trying to create a formula that when something is entered in one cell the current time is stamped in another cell. So far here's what I came up in my C4 cell =IF(A4<>"", NOW()-TODAY(), "") When something is entered in cell A4, the current time enters in at cell C4. I copied the formula by using fill down, but when I enter in something in the next cell of A5, cell C5 and C4 gets updated with the same current time, when i enter the something in A6, cells C6, C5, C4 gets updated with the same time and ETC, ETC How would I use absolute valu...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Importing text from an Explorer window
Howdy all. OS: XP, Excel 2002 SP3 I have my music stored in the My Music directory. I have Artist, Album Title, etc. attributes turned on. What I'd like to do is to take all of this information and input it into an Excel spreadsheet. When I "Select All" from the window, and "Copy", it wants to copy the actual songs, and not the text. Also, the "Paste" option in Excel is grayed out anyway. Is there a way that I can treat the info in a Browser window as just text, and then copy that info into a spreadsheet? TIA. -- Don Cox Here is one I have used...

Invoice Due Date #2
Hello, I'm using Money Small Business 2006 and need some help. How can I specify the due date on a invoice? It seems to automatically use the date that the invoice is created on. All help appreciated. Thank you. > How can I specify the due date on a invoice? It seems to automatically > use the date that the invoice is created on. Below the Line Item table there is a link "To add or edit billing and shipping addresses, notes, or other information, go to the Invoice Details". Click that link and you can specify the Payment Terms. I don't see where to specify a cet...

Change color of row (Col A thru Col Q) based on text value in Col J
I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? One way: Target.EntireRow.resize(1,17).Interior.ColorIndex = 36 JingleRock wrote: > > I...

Format a concantated text string
Sorry if this is boring but I'm stuck. I have a cell that counts something, it also has a piece of text at the front of the count but I want the count to be formatted as currency and it won't. ="Major "&COUNTIF(C:C,"Major")*245 I've tried ="Major "& Format(COUNTIF(C:C,"Major")*245****) but I can't get it right and think it's a VBA command! Help please! Thanks Dean -- dkso@ntlworld.com http://www.dkso.co.uk/ http://homepage.ntlworld.com/dkso Thank you! Dean "Bob Phillips" <bob.phillips@notheretisca...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...