How to convert DateTime to just Date in LibreOffice?
Solution 1
Having issues as well (4.0.2.2 with English (US) locale): It somehow won't recognize the long ISO format for dates; only if I edit the date and remove the time. I've read about date acceptance patterns, but couldn't get them to work (why wouldn't it work by default to begin with?).
As a workaround (note: data will be lost!) you could use search and replace:
- Select the cells with dates and hit Ctrl+H.
- Use the search pattern
(\d{4}-\d{2}-\d{2})T\d{2}:\d{2}:\d{2}\+\d{2}:\d{2}
. - The replacement is
$1
. - Tick Regular expressions (click on More Options if you don't see it).
- Click Replace All.
- It should now recognize the dates (but you no longer have to format them anyway).
Solution 2
it is quite more simple... just use the function =DATEVALUE(a)
with "a" the cell with your text DateTime. Then format your resulting cell as wanted.
The same way if you want just the "time" section, use =TIMEVALUE(a)
and if you want in one cell the entire date and time values, not as text but as time and date values, use =DATEVALUE(a) + TIMEVALUE(a)
and format as wished.
regards!
Related videos on Youtube
Lars Öberg
Updated on September 18, 2022Comments
-
Lars Öberg over 1 year
In LibreOffice, Calc under Linux, I have a table with DateTime values (eg. 2011-06-15T15:37:56+00:00). How shall I convert these values to just Date values (eg. 2011-06-15)? I suspect it is very simple..
-
Admin over 10 yearsHave you tried =INT(A1), where A1 is the cell with the combined Date-Time value?
-
fixer1234 almost 5 yearsAncient question that just got bumped. One thing that isn't clear (and future readers may have different needs): Is the actual requirement to work with just the date, so different times of day are all treated the same, or simply to display only the date? If you need to eliminate the time (for sorting, filtering, or date/time math), using INT or DATEVALUE, as described in the answers, does that. If it is simply a matter of displaying just the date, that can be done with formatting, and the data doesn't need to be altered.
-
-
Lars Öberg over 10 yearsThanks for your suggestion, but I have tried that without success!
-
Mario over 10 yearsOkay, just saw that I've got Calc installed in my virtual machine. Having a look - now I'm curious... :)
-
Leo Chapiro over 10 years@Lars: It works exactly like Mario wrote (+1)
-
Lars Öberg over 10 years@Mario With a little modification your resplace worked fine. Se answer below
-
Lars Öberg over 10 yearsThe Search expression (reg exp) should be: ([:digit:]{4}-[:digit:]{2}-[:digit:]{2})T[:digit:]{2}:[:digit:]{2}:[:digit:]{2}\+[:digit:]{2}:[:digit:]{2} And the Replace, just as Mario said: $1 Then it worked. I think the syntax differ a bit from Excel. However it should indeed be possible to make a normal conversion of the DateTime type to a Date type .. can it be a bug in LibOffice Calc? Thanks.
-
Mario over 10 yearsHm? I've used
\d
in LibreOffice Calc without problems. Maybe this differs from version to version as well, but I think it should accept both. As for the detection, this might indeed be a bug or some oversight (or us misunderstanding the whole thing). At least I don't see any reason for not supporting the long ISO format. -
Mario over 10 yearsIt's actually a workaround but I wouldn't consider it a direct answer, so wouldn't hurt to keep the question open for a bit longer. Maybe we're both missing something important.
-
Lars Öberg over 10 years@duDE: Let's wait for a day or to, as Mario suggested. That's wise.