Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2022, 11:38 AM
sam123456 sam123456 is offline Excel reading date incorrectly Windows 10 Excel reading date incorrectly Office 2016
Novice
Excel reading date incorrectly
 
Join Date: Feb 2022
Posts: 1
sam123456 is on a distinguished road
Default Excel reading date incorrectly

Hi,
I have a csv file with ~500,000 rows. I am facing an issue with Excel interpreting dates incorrectly.

I download this data from an online platform, where the date is formatted as DD/MM/YY.

I download data from the platform, then copy and paste to an existing csv file which is then fed to a tableau dashboard.



As soon as I copy and paste data to the csv file, Excel is interpreting and changing the data to YYYY/MM/DD format.

For example:
01/02/19 (1st Feb 2019) is being changed to 2001/02/19

The date column in the csv file is set to Short Date.

I tried converting to text so I could manually change the dates - as soon as I do that the date changes to a number like 39610. I cannot change one row at a time as there are ~500,000 rows.

Is there another fix?

Thanks
Reply With Quote
  #2  
Old 02-12-2022, 12:39 AM
Alansidman's Avatar
Alansidman Alansidman is offline Excel reading date incorrectly Windows 10 Excel reading date incorrectly Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

Use Power Query to import the data to the Power Query Editor and then bring it into native excel. This should keep the date data formatted correctly.
Reply With Quote
Reply

Tags
date, formatting

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel file size increasing incorrectly jmcsa3 Excel 8 09-09-2020 09:21 PM
Excel reading date incorrectly MSProject imports Finish date incorrectly cdfj Project 1 07-12-2019 04:04 AM
Using Add-ins across multiple computers, reference incorrectly: Excel 2016 AllKnight Excel Programming 0 02-28-2018 04:41 AM
Excel reading date incorrectly How to save a PPT file in 'Reading View' to be opened always in 'Reading View' ItzVickey PowerPoint 2 08-08-2012 09:23 AM
Excel reading date incorrectly Mailing: Reading values from Excel to Word Jamal NUMAN Excel 9 09-02-2011 02:08 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft