reformatting Excel numeric dates or parsing

[genevatexan]

We have imported an Excel spreadsheet with dates in one column that were imported as numbers such as '39287', which can also be displayed in Excel as July 24, 2007. We concatenated this import to a table where all our dates are stored as 20070724. Now we have both formats in one column, with over 70,000 records in the 20070724 format and a few thousand in the Excel format. How can we convert the fields in the Excel format to the appropriate YYYYMMDD format we use?
Many thanks for your help, we can find a good way to clean this up...
Genevatexan

 

[namliam]

text(insertcell, "yyyymmdd")

this will convert the actual dates into your ISO date.

Good Luck

 

[genevatexan]

Reformatting Excel dates (In Access)

thanks for the response namliam. This Excel spreadsheet was imported into an Access table. So can you guide me a little more on how to use the function you mention? Would I use this in a query to update the table by burying this in a new Expression in one of the query columns? Or is your suggestion to change the Excel spreadsheet using this function you mention and then re-import the records?
Many thanks for helping along,
GT

 

[namliam]

Text is an excel function, the access equivalent is Format which you can use in a query.

If you are using this in access, I would urge you to use REAL dates over ISO dates.
Real dates you can modify, report, add and substract, get weeknumbers, etc.
ISO dates you cannot. The fun of access is you can use real dates and display them using the "format" option of the column in the table.

 

[gemma-the-husky]

if you have two alternate date formats in a column then clearly you cant process them both in the same way

if the excel date numbers are always in a certain range then you can selectivley process them - try access date functions

it will be something like

cdate changes an expression into an access date
format formats it in a certain way

so
format(cdate(datenum),"yyyymmdd")), should turn the excel numbers into the "2007 format

and
dateserial(left(cstr(datenum),4),mid(5,cstr(datenum),2),right(2,cstr(datenum))
should turn the "2007 numbers into dates

----
now you have real dates that can be managed properly - eg you can find the difference between two dates simply by subtracting them