Convert imported text to date time

[Cindy]

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

 

[Daniel Pineault]

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.

 

[John W. Vinson]

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.