Excel date error when the day of month is below 24

Copper Contributor

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.

2 Replies
Solved. I'm blaming myself for not having checked the obvious at the start, but somehow the fact that part of the dates were correct, I made wrong assumptions at the start.

In case someone bumps into a similar thing, check your "Regional format".
Mine was initially "English (Finland)", because I wish to have an English UI, but use local formats.
In the "Time & Language - Region" I had only English formats avalable. So I had to go tothe "Clock and Region - Region/Change date, time or number formats". There you wil find all supported formats.

I am still intrigued to know what caused the error, but glad to have got rid of it.

@Jukka_Lehtis 

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.