Yasmine has attended:
Excel Intermediate course
Excel Advanced course
Power BI Modelling, Visualisation and Publishing course
Excel changing imported dates into numbers
I import a large amount of data in to excel from a single source (a trigonal based SQL software). This single source stores dates as dd/mm/yyyy but whenever I import the data to excel these dates change to numbers and hashtags. It doesn't seem to make a difference whether I export from the software directly in to excel or whether I copy and paste the same data, the dates still change to numbers. When i reformat the cells to be dates, some of them do change but a large number stay as hashtags (this is not due to column size, they are literally hashtags). The dataset is too large to manually go through and cross compare them with our original data entry and manually change all the dates, but none of us (including our IT department, the company who own and run the SQL software package and other colleagues proficient in Excel) can work out how to change this or what's going on. Can you help?
RE: excel changing imported dates into numbers
Hi Yasmine.
Thank you for the forum question.
Can I please ask you to send STL an Excel file with a sample of the dates having the problems.
I will need to see them to help.
Please send the file to: info@stl-training.Co.uk
kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: excel changing imported dates into numbers
Hi Yasmine,
I have been testing your dates across all columns with dates. The dates are not only imported as ####, numbers, dates, but also as text strings. I have tried different cleaning tools, Power Query and more and can 100% tell you that it is not an Excel problem. It can be in the SQL database or how it is imported.
If I was you, I would make a connection straight to the database from the power query tool in Excel. If a straight live connection give you the same problem, something is wrong with the entries in the SQL database or the SQL database it self.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: excel changing imported dates into numbers
Hi Yasmine,
Power Query can transform the text strings and numbers into dates but there is no way you can transform the ######.
There is no information hidden in the #####.
The problem as I told has to be found before the data got to Excel.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
