| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change satellite julian date format YYYYDDDHHMMSS to excel date ti | Excel Discussion (Misc queries) | |||
| How do you change format on Excel .CSV file to import to access | Excel Discussion (Misc queries) | |||
| Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
| Analyze with Excel from Access | Excel Worksheet Functions | |||
| access dates in excel, how to change format? | Excel Discussion (Misc queries) | |||
Linear Mode
