When I download data from our internal system into txt and then import this data into excel, the date column changes and does not read as date and time. This has only occured since Ive started using excel 2016.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Date and time format changing
0
Answers
0
Presumably (hopefully) you get a number, like 42321.5321112 or perhaps separately, 42321 and 0.5321112. The integer represents a date, the fraction a time. You can format either by going to Format > Cells and either select one of the available standard formats or make your own "Custom" date or date/time format.
Discussion
No, previously when I imported the data the date would pull in in this format - 25 03 2015 however when I pull in the data now it pulls in like this - 13/01/17.
I would normally pivot my data and the dates would group now it doesnt. I also import the data into access and it does not read the date as date and time but text and the data does not pull through.
I would normally pivot my data and the dates would group now it doesnt. I also import the data into access and it does not read the date as date and time but text and the data does not pull through.
Mim Nov 1, '17 at 6:48 pm
It's impotant to know whether you import dates as date values, as described in my previous post, or text strings. What you describe above looks like you have date values but your default date format settings haven't been adjusted to your liking when you installed Excel 2016.
Go to the Control Panel, choose 'Clock, Language and Region'. Adjust the default short date format from dd/mm/yy to dd mm yyyy.
The format you set for cells in the Regional Settings will be applied if the worksheet cells are formatted as "General". Other formats set for the cells over-ride the defaults. Conversion of dates to text is likely a different problem. Perhaps it will go away when you resolve the date format issue.
Go to the Control Panel, choose 'Clock, Language and Region'. Adjust the default short date format from dd/mm/yy to dd mm yyyy.
The format you set for cells in the Regional Settings will be applied if the worksheet cells are formatted as "General". Other formats set for the cells over-ride the defaults. Conversion of dates to text is likely a different problem. Perhaps it will go away when you resolve the date format issue.
Variatus (rep: 4889) Nov 1, '17 at 10:27 pm
Mim ~ When you import txt into excel, depending on which option you choose (Delimited or Fixed width) there is an option to format the data (in DATA PREVIEW screen) and you can highlight your column with DATES and select the date format you desire. Perhaps this was changed? OR it defaults to the incorrect format by default? Sometimes Access is a better importer - you can set up/ Save an import template....I believe it's under advanced tab, but there you can define your fields to help get the data in your desired format. OR you can change formats as mentioned vy Variatus. Good luck -
queue (rep: 467) Nov 2, '17 at 9:58 am