Convert Date/Time to just Date inCalc

I’m having trouble converting an imported field that has YYYY-MM-DD HH:MM:SS to just YYYY-MM-DD. I’m using version

Example: cell contains 2022-12-09 00:49:10
I used Format/Cells/Date/Format 1999-12-31. Format Code is YYYY-MM-DD, and it shows ISO 8601. Result: In the cell itself, nothing has changed. The line where you can see the actual data (not sure what that’s actually called) shows the same data with a single quotation mark in front of it('2022-12-09 00:49:10). If I manually remove the single quotation mark, the date formats correctly. However, because I have a whole bunch of cells that need reformatting, I’m trying to find a better solution than one ata time.

I tried Find and Replace, with Find '2, Replace 2, but it didn’t find it.

According to Help, the Format Cell function should work.

What am I doing wrong… or… is there some trick?

Like 99% of first time csv importers you did not check option “detect special numbers”. Your dates are text. Import correct values and number formats will apply

1 Like
  1. Select your date/time range (with or without the apostrophe, but only one column at a time).
  2. Choose menu Data - Text to Columns
  3. Check [·] Space
  4. Select Column type Date (YMD) for the first column and Hide for the second column
  5. OK


wow! thanks! I NEVER would have figured that out.

1 Like

Nevertheless, you should almost always check the “special numbers” option when importing text in order to avoid wrong data import.
The text-to-column solution can be used in 2 ways. Leroy’s solution converts the wrongly imported text cutting off the time fraction. If you choose no column delimiter, you convert the text column into numeric values at full precision including the time fraction and you can hide away the time by means of formatting. The right method depends on your needs regarding precision.

1 Like

thanks for the additional info. I guess I thought by clicking on the Format Cells / Date it would actually convert it to a date. Imagine that.

Excel would have done it that way.

Nope. Excel does not convert text into numbers by mere change of number formmat. In fact this would be a bug.

1 Like