[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Access function or formula

I get a text file from a linked SQL table that contains 201101021515  which is YYYYMMDDHHMM or "01/02/2011 03:15 PM"  Is there a formula / function to
convert 201101021515 to "01/02/2011 03:15 PM"

My goal is to export to Excel from Access, so i would like Excel to see the "01/02/2011 03:15 PM" as a date/time value that I can perform time calculations with.
0
dastaub
Asked:
dastaub
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
If that value is held as text:

CDate(Left(MyColumn, 4) & "-" & Mid(MyColumn, 5, 2) & "-" & Mid(MyColumn, 7, 2) & " " & Mid(MyColumn, 9, 2) & ":" & Right(MyColumn, 2))
0
 
Gustav BrockCIOCommented:
You could also use Format:


CDate(Format([YourFieldName], "@@@@/@@/@@ @@:@@"))

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
:-)
0
 
Patrick MatthewsCommented:
gustav,

Very elegant!

Patrick
0
 
Gustav BrockCIOCommented:
Yes, I like it too, though not invented by me.
A good reminder of the power of Format.

/gustav
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now