Analyze with excel (change access date format)
 

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

Analyze with excel (change access date format)
 

Select the columnDataTExt to columnsNextNext 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...
--
If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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

Analyze with excel (change access 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

"Jacob Skaria" wrote:

Select the columnDataTExt to columnsNextNext 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...
--
If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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

Analyze with excel (change access date format)
 

You will need the data in that column before attempting 'Text to columns'

If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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

"Jacob Skaria" wrote:

Select the columnDataTExt to columnsNextNext 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...
--
If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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

Analyze with excel (change access date format)
 

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

"Jacob Skaria" wrote:

You will need the data in that column before attempting 'Text to columns'

If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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

"Jacob Skaria" wrote:

Select the columnDataTExt to columnsNextNext 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...
--
If this post helps click Yes
---------------
Jacob Skaria


"Driftwood" wrote:

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