Ask Your Question
1

custom date and time formats

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

suseuser04 gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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 https://ask.libreoffice.org/en/questi....

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
1

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

suseuser04 gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,576 times

Last updated: Dec 01 '17