Custom date and time formats

[suseuser04]

Hello:

In libreoffice calc (currently using 4.3.7.2) I would like to use custom date and time formats in cells as follows:

Date: YYYYMMDD, that is without separators between year, month and day, eg 20171130.

Time: HHMM with 0-24 h scale without separator between hour and minutes, eg 2132

I tried to set the above formats in format/cells/numbers/category date and time respectively but it doesn’t work.

For date it accepts my custom format YYYYMMDD if I type it in the format code field, and it can be saved. But if I enter a date in a cell formatted with my custom format the date changes strangely, eg if I type in the cell 20171130, it becomes -84100904 after hitting enter.

For time formats it even doesn’t accept my custom HHMM time fromat. If I type my custom format code in the format code field, the category changes (from time) to user-defined.

What is the solution for this?

Thanks,

suseuser04

[jimk]

The date format is correct. If you enter 2017-11-30 in that cell, then it will correctly display as 20171130. The format does not affect how dates are entered. For that, go to Tools → Options → Language Settings → Languages → Date acceptance patterns.

Dates cannot easily be entered as 8-digit numbers, because that conflicts with entering actual numbers. For workarounds, see Calc: How do I specify a "User-defined" input format for dates?.

The reason for the strange result is that 20171130 is interpreted as a number, and the number is way out of the normal range for dates. For example, 43069 is the underlying number for the date 2017-11-30.

The time format is also correct. HHMM is a user-defined format, and that’s what you want. Entering 3pm in the cell will display as 1500.

[suseuser04]

Jim,

Thank you for your quick answer.

The question you referred is exactly the same thing I wanted to know.

Now it’s clear why it doesn’t work.

I did not know that input formats and visible/output formats are two different things. I expected that if I specify a format it applies both to input and output format.

What it would be really good if I could specify both input format and output format.

I downloaded the file with the macro from the referred page (ask62701SpecialDateRecognition002.ods) but I need time to understand and try it.

Thanks again,

suseuser04