Date formatting problem

[Stephr]

when pasting 300+ date and times from another document to Calc in form: 2017/01/01 00:00, cells are stuck as text format. How do I convert format to time or delete date for all cells?

[Lupp]

It was in fact a conversion problem. As soon as the conversion to the internal numerical date-time representation is done, the values can be displayed in any format describable by a format-code for numbers,
Please note: While the example is doubtable, its format as shown in 2017/01/13 23:17 is only second to 2017-01-13 23:17. The ISO 8601 extended format is the real one that makes interchange of date-time as text unambiguous and independent of stubborn spreadsheet settings. .

[librebel]

Hello @Stephr,

Please check if the input format “Y/M/D” is specified among the valid input formats for dates, in the menu “Tools : Options... : Language Settings : Languages : Date acceptance patterns”. If not, then type ;Y/M/D to add it ( using a semicolon as separator ).

From now on, Calc can recognize strings of the form “2017/01/01” as a valid Date format.

Now you could extract the dates and the times from your 300+ cells as follows:

( assuming cells A1:A300 for example ):

1. Type into cell B1 the formula =DATEVALUE(LEFT(A1;10)), then drag the small cell handle downwards to cell B300;
2. Type into cell C1 the formula =TIMEVALUE(RIGHT(A1;5)), then drag the small cell handle downwards to cell C300;
3. Set the Number Format for cells B1:B300 to a Date format such as “YYYY/MM/DD” ( by selecting the cells, right-click inside the selection, choose the menuitem “Format Cells…”, select the tab “Numbers”, select Category “Date”, then enter the Format );
4. Set the Number Format for cells C1:C300 to a Time format such as “HH:MM”;

HTH, lib

[Lupp]

…
5. Make sure that that the format HH:MM is not applied to a value gotten the way @mikekaganski described. It would be a “lying” format only suppressing the still present date part of the value instead of removing it. Based on Text to Columns... you would need to use the ‘Fixed width’ option to split the imported text correctly. (This is kind of equivalent to what @librebel does by formulae.)

[Lupp]

@librebel: Did you check in what way the added Y/M/D acceptance pattern would act in an English (Something) locale having preset either M/D/Y or D/M/Y?
My tests with English (UK) showed a complex, partly nonsensical behaviour in this case, basically preferring the preset acceptance pattern even if it was deleted from the respective string in the ‘Options’.
(To hell with hyped-up localisation!)

[librebel]

Thank you for your comments @Lupp,

I have only tested this in my own locale nl-NL (en_US.UTF-8).

If adding the Y/M/D pattern causes nonsensical behaviour in some locales, the above procedure could be adapted as follows:

adapted Step1: type in B1 the formula =DATEVALUE( LEFT(SUBSTITUTE(A1;"/";"-");10)), then drag the small cell handle;

adapted Step2: type in C1 the formula =TIMEVALUE(RIGHT(SUBSTITUTE(A1;"/";"-");5)), then drag the small cell handle;

[mikekaganski]

Use Data-Text to Columns tool to convert existing text in cells to other formats. If required, change columns’ type in the dialog.

EDIT: As it seems from comments that the procedure is unclear:

1. Select the column that consists of the “2017/01/01 00:00”-like text strings.
2. Open Data → Text to Columns... dialog.
3. Select Separated by: Space.
4. If required, apply Date (YMD) to the first column in the Fields section (times would be converted correctly anyway).
5. Click OK.

This will split the column into two, first of which would contain dates (in textual form, or converted to date format, depending on your date acceptance settings and chosen type in dialog), second - times in proper format. You can delete first column afterwards if unnecessary.

Generally, this does not require any general settings changes, including polluting of date acceptance patterns with patterns like “Y/M/D” that don’t follow ISO standard. The Text to Columns tool is designed specifically for conversion problems like this.

[Lupp]

OK. I missed to think of the Space as separator in the context. Sorry! Fixed width not needed.