Ask Your Question

custom date and time formats

asked 2017-11-30 23:46:00 +0200

suseuser04 gravatar image


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?



edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-12-01 00:44:46 +0200

Jim K gravatar image

updated 2017-12-01 00:47:10 +0200

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

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.

edit flag offensive delete link more

answered 2017-12-01 15:39:19 +0200

suseuser04 gravatar image


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,


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-30 23:46:00 +0200

Seen: 2,737 times

Last updated: Dec 01 '17