Custom date and time formats


In libreoffice calc (currently using 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?



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.


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,