• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Importing an excel file with a problematic date column

Hi,
I have an excel file (attached ) with one column.
the problem is that this column have defferent formatting for a date.
this what i`m getting from external apps as export.

now i`d like to import this file into access.
but when i do that. it doesn`t recognize the column as a date , but as i text.which i don`t want to.
that`s because of the problem with the format.
how can i import the column as a date (fixing the problem through the importing process in access) , if one may help out , maybe using a vba code . or somethen:}
thnx in regard :}
DateProblem.xlsx
0
drtopserv
Asked:
drtopserv
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
import the excel file to a temp table.
use a query to format the dates imported to the temp table and append the result to the destination table.

what date format do you have in your destination table?
0
 
drtopservAuthor Commented:
dd/mm/yyyy
but how can i use a query to format it?
may a sample?
0
 
Rey Obrero (Capricorn1)Commented:
select cdate(format([datefield],"dd/mm/yyyy"))
from temptable
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
drtopservAuthor Commented:
but did you see the column in the excel file, it has 2 formats togother in same column
0
 
Rey Obrero (Capricorn1)Commented:
yes i did.. try my suggestion so you can see the result after running the query.

you can then convert the query into an append query.



what is your regional setting?

try this query and see which is more likely to return the correct value

SELECT Sheet2.Started, CDate(Format([Started],"dd/mm/yyyy")) AS Expr1, DateValue([Started]) AS Expr2
FROM Sheet2;
0
 
drtopservAuthor Commented:
I LOVE YOU man!! heheh
it works!!! thnx alot..
plz may u have alook at my open Q:ID: 28158731
0
 
drtopservAuthor Commented:
btw, in excel i couldn`t use :CDate or DateValue (didn`t work for me:} )
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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