Convert Date/Time to just Date inCalc

lliefveld · December 11, 2022, 3:15pm

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 7.3.4.2.

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?

Villeroy · December 11, 2022, 3:34pm

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

LeroyG · December 11, 2022, 3:57pm

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

lliefveld · December 11, 2022, 5:04pm

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

Villeroy · December 11, 2022, 6:03pm

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.

lliefveld · December 11, 2022, 7:25pm

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.

Villeroy · December 11, 2022, 8:33pm

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