How to convert DateTime to just Date in LibreOffice?

5,509

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!

Share:
5,509

Related videos on Youtube

Lars Öberg
Author by

Lars Öberg

Updated on September 18, 2022

Comments

  • Lars Öberg
    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
      Admin over 10 years
      Have you tried =INT(A1), where A1 is the cell with the combined Date-Time value?
    • fixer1234
      fixer1234 almost 5 years
      Ancient 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
    Lars Öberg over 10 years
    Thanks for your suggestion, but I have tried that without success!
  • Mario
    Mario over 10 years
    Okay, just saw that I've got Calc installed in my virtual machine. Having a look - now I'm curious... :)
  • Leo Chapiro
    Leo Chapiro over 10 years
    @Lars: It works exactly like Mario wrote (+1)
  • Lars Öberg
    Lars Öberg over 10 years
    @Mario With a little modification your resplace worked fine. Se answer below
  • Lars Öberg
    Lars Öberg over 10 years
    The Search expression (reg exp) should be: ([:digit:]{4}-[:digit:]{2}-[:digit:]{2})T[:digit:]{2}:[:digi‌​t:]{2}:[:digit:]{2}\‌​+[:digit:]{2}:[:digi‌​t:]{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
    Mario over 10 years
    Hm? 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
    Mario over 10 years
    It'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
    Lars Öberg over 10 years
    @duDE: Let's wait for a day or to, as Mario suggested. That's wise.