• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • 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 Access MVP)Database ArchitectCommented:
:-)
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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