Analyze with excel (change access date format)

  • Thread starter Thread starter Driftwood
  • Start date Start date
D

Driftwood

Hi,
I have a data base in Access that I am analyzing in excel.
I move (create copy) the analyzed worksheet from access into my excel
workbook.
There I have in Column A: dates ranging from 1/1/09 - 12/31/2015 in the
GENERAL format (for the macro to browse through and find the input date of
the user).
Column B-CD all have VLOOKUP formula to pull certain information from the
worksheet that came from access.
My problem is:
The worksheet that I analyze in excel can not be formatted so that the date
column showes in GENERAL format (39818) and not the 1/5/09 format.
I tried :insert new sheet - formatted that - copied & special paste Values
only- used
= LEFT(tblMRRMIR,A3, 10)in hopes I could format the results of a formulae.....
nothing so far has worked.
How do I get the analyzed access data to convert to Excel GENERAL date
format?

Thanks
Driftwood
 
Select the column>Data>TExt to columns>Next>Next will take you to Step 3 of 3
of the Wizard. Select the data format as 'Date' and selec the format in which
your data is 'MDY' and hit Finish. This will convert the dates to excel date
format...
 
Jacob,
Thank you!
I will put your answer into my personal HowTo workbook for future reference.
You would not believe how many times I had struggled with date issues in
macro's.
THIS IS THE ANSWER! I will actually write this formatting into the code, so
before updateing new sheets from access it runs through the date columns and
re formats them just like this.
cheers
Driftwood
 
You will need the data in that column before attempting 'Text to columns'

If this post helps click Yes
 
Yes, Jacob,
I have all sheets needed to get final results placed in workbook prior to
running any codes through them.
Thanks again your a great help.
Driftwood
 
Back
Top