I am trying to import csv data into Libre Calc. No matter what I try, the date field isn’t formatting as a date. I’ve checked the “detect special special numbers” field, have tried formatting as a date once it’s been imported and more but it remains a non-date field. What am I doing wrong?
I’ve searched online and through the help files quite a bit and am quite frustrated!
I’m using version 24.8.3.2 (x86_64)
You must set the relevant date-pattern for the Date column in the Import filter. The “Number recognition” feature works based on this information.
It would be good to know, how the actual date looks like.
We don’t know your data.
Both formats work equally well on appropiate data. But please start thinking and open your eyes:
.
dmy means day first, then month, then year. So your 11/10/24 would be October, 11 in 2024.
.
Now read your “not dates” again:
10-15-2024 will need a calendar with 15 or more months for dmy, so maybe your column is not in dmy-format, but in mdy.
Then 10-15-2024 is Oct 15th 2024, but this would also mean the dates you described as “CORRECTLY IMPORTED” were completely wrong instead, because 11/10/24 would NOT be October, 11 in 2024 but Nov., 10th 2024.
.
Worst case would be, if your data is mixed from different sources and has different format in one column. Then start new or check every line/range wich format is correct/likely.
Your examples and screenshot seem to indicate mdy is used for input as @Villeroy , but we don’t see all data…
.
And as conclusion you might understand, why some preach only to use yyyymmdd as the single useful format, when exchanging dates via csv.
It is a csv file so it doesn’t contain any location or formatting information so you must make as educated guess at to its origin. You must set that original formatting in the text import dialogue; you can change the format after it has been imported
.
Looking at your screenshot, I see a date 08/30/24. The number 30 must refer to day of they month, the 30th. Therefore 08 must refers to the month, and obviously 24 is the year 2024. The order of the date is then in USA format, that is, Month/Day/Year. In the text import dialogue that is shortened to Date (MDY) so that is the type you must choose for the date column. All the other dates in the same column are formatted the same so we know that A2 will be 11th September 2024, not November.
I do not use USA dates but Excel always assumed I did despite my location settings. This was one of the issues that drove me away from Microsoft Office
No wonder. Your data is not in ymd-format. So just forget about ideas to avoid your situation. But that is why I told you so rude to “start thinking and open your eyes” YOU are supposed to be the intelligent part of the toolchain, who decides wich is your source format. A computer can not always decide between dmy and mdy. If this is not clear for you, I wish you good luck - you will need it.
.
This is partly good, because the format wich is shown is not decided on import. Like Excel is converting the sequence of characters you see as a date in a simple(?) number counting days since the beginning of the 20th century. Try to set the format of your imported column to a number format. Then you can maybe see how Calc can calculate with dates so easily. The change formatting again to your choice of date-formatting.
.
Basic rule is: Formatting don’t change the value, only the presentation. So your task for import is to give Calc information to find the right number or you will get nice looking but wrong data.
Your problem is: LibreOffice is protecting you from converting 10-15-24 with dmy to a false date/number by not converting this. If Excel can magically do this, use it. Magic powers would be something worth to pay for.
Show your complete column.
Maybe import once as text, delete all other columns, then save as .ods and upload the file here for inspection.
Otherwise I recommend to try Excel. But check the imported data, if you can…
@EarnestAI Thank you but the issue is that it’s not importing as a date. Libre Calc thinks it is text.
I’ve tried to explain it different ways but nobody seems to understand what I am saying.
Please try it with date set to Date (MDY), then comment.
.
The probable reason it was not importing as a date is that the column was set to Date DMY. As has been explained, 08/30/24 cannot be written in the format Date DMY as there is no month 30 in the year.
.
If there is still a problem, then tick the box Trim spaces, otherwise a sample file would be needed.
@earnestai I understand now. Sorry for not getting it before. Thank you for your patience and kind response.
The Text Import dialogue settings (except column settings) are sticky; what they were set to last time will be what they are set to next time.
.
In your deleted post, the Text Import dialogue screenshot showed the Locale (for the imported csv) as English (USA). That, along with Detect special numbers should have been sufficient for Calc to apply the MDY date format for the column even with the Standard setting (the Date (MDY) won’t display that in the dialogue). Nevertheless, I always check the column type every time to make sure I have specified what I think is the column type.
