Excel changing imported dates into numbers | Excel forum

Resolved · Medium Priority · Version 365

Thu 8 Sep 2022, 08:34 Delegate Yasmine said...

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?

Thu 8 Sep 2022, 09:00 Trainer Jens said...

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

Thu 8 Sep 2022, 13:03 Trainer Jens said...

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

Thu 8 Sep 2022, 13:24 Trainer Jens said...

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

Thu 15 Sep 2022: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Screen Splitters in Excel

Screen splitter icons can be set from the ribbon bar, or dragged from the scroll bars. The icon just above the up arrow on the right scroll bar controls the horizontal splitter; the icon to the right of the right arrow on the bottom scroll bar controls the vertical splitter.

These icons can be double-clicked to split the screen at the top left of the cell currently in use.

View all Excel hints and tips