Apr 15 2021 01:06 AM
I've been importing transaction data from my bank account for years in csv files, but now with Office 2019 some of the dates are wrong. There are two things I find odd.
First is that whenever the day of month number is 24 or greater, the date comes out correctly. When it is 23 or smaller, it is shown as decimal number below 1. For example April 6th 2021 (06.04.2021) results to "0,276168981481481" shown in the cell. Because this is below 1, should I change cell format to date, it will show 01.01.1900. The other odd thing is that when I open the same csv file with MSWord, it'll show correctly.
Also - When I have corrected the dates to show correctly in Notepad and then I copy-paste them from Notepad to Excel, Excel will still show dates again incorrectly. I'd like to understand why this is happening and how to fix it.
This is not a total showstopper, as I can use "Get data" function, which seems to be able to handle this, but this is more work and I'd like to trust Excel to always do a correct job. I attach a sample of some wrong dates and what they should be.
Apr 15 2021 01:32 AM
Apr 15 2021 01:38 AM
What is the default time format on your computer? It looks like Excel interprets the value 23.3.2021 as 23 hours, 3 minutes and 2021 seconds on your computer. This is equivalent to 23:36:41. Excel stores this time as the number 0,98380787, which is what you see.