Press "Enter" to skip to content

Date and Time Formats in LibreOffice Calc

Last updated on March 8, 2020

Date and Time formats and how they are represented in LibreOffice Calc is explained in this tutorial.

Date Formats

Open a blank Calc sheet and enter the below items – a date, a time and a date-time.

“3/2/2018”

“6:00”

“3/2/2018 6:00”

Once you entered, Calc would reformat them as below using default Calc formatting for each type.

Date Time Data
Date Time Data

Dates are stored as numbers in Calc and each day represents a number counted from the date Dec 12, 1899. However, you can change it from the menu Tools -> Options -> LibreOffice Calc -> Calculate. See below.

Default-date-calculation-Calc
Default-date-calculation-Calc

Right click on the date at A1 cell and click Format Cells. The default format of the selected cell would open up as below.

Format Cells-DD-MM-YY
Format Cells-DD-MM-YY

Convert the date to number

To convert the date to a number, simply choose Category=Number and you can see the numeric representation of the date. For this example 3/2/2018 is converted to the number 43134 which is nothing but the number of days elapsed from Jan 0, 1900.

Format Cells-Date Numbers

Format Cells-Date Numbers

Time and Conversion

If you apply the same i.e. format cells to numbers to the Time and Date Time as well you can see below results.

Converted to Number-All
Converted to Number-All

Time is represented in a scale of 0 to 1. So 6:00 AM is converted to .25 i.e. quarter of 24.

Custom Formatting of Date, Time

Calc provides a pre-defined set of date formats for general usage which you can access from the Format list below. However, if you want to customize your own format you can use any of the codes below and create your own custom format.

SEE ALSO:  How to Type Unicode Characters in LibreOffice

To create a custom format, right-click any cell and open up Format Cells dialog. In that dialog, start editing the Format Code (red arrow below). Once you start modifying it, you can see a “user-defined” category is automatically created. Now you can modify your format according to your needs. Refer to format codes at the end of this article.

You can also rename the category = “User-defined” by clicking a small note icon (blue arrow below) and click the tick button to Add.

Format Cells-Custom Format
Format Cells-Custom Format

In this example, we have applied the custom date format and it is applied to cell A1 below. It is worth to notice that though the representation is changed to “03/02/18Y”, the underlying value remains “03/02/2018”.

Custom Format Applied to a Date
Custom Format Applied to a Date

The same principle can be applied to Time formats as well.

List of Date Format Codes

Below are the format codes for dates that you can use in formatting cells.

FormatFormat Code
Month as 3.M
Month as 03.MM
Month as Jan-DecMMM
Month as January-DecemberMMMM
First letter of Name of MonthMMMMM
Day as 2D
Day as 02DD
Day as Sun-SatNN or DDD
Day as Sunday to SaturdayNNN or DDDD
Day followed by comma, as in “Sunday,”NNNN
Year as 00-99YY
Year as 1900-2078YYYY
Calendar weekWW
Quarterly as Q1 to Q4Q
Quarterly as 1st quarter to 4th quarterQQ
Era on the Japanese Gengou calendar, single character (possible values are: M, T, S, H)G
Era, abbreviationGG
Era, full nameGGG
Number of the year within an era, without a leading zero for single-digit yearsE
Number of the year within an era, with a leading zero for single-digit yearsEE or R
Era, full name and yearRR or GGGEE

If you change your locale of LibreOffice e.g. German, you need to use a different code as per below.

LocaleYearMonthDayHourDay Of WeekEra
English – en
and all not listed locales
YMDHNG
German – deJ T   
Netherlands – nlJ  U  
French – frA J O 
Italian – itA G OX
Portuguese – ptA   O 
Spanish – esA   O 
Danish – da   T  
Norwegian – no, nb, nn   T  
Swedish – sv   T  
Finnish – fiVKPT  

List of Time format codes

To format time, use below format codes:

FormatFormat Code
Hours as 0-23H
Hours as 00-23HH
Hours as 00 up to more than 23[HH]
Minutes as 0-59M
Minutes as 00-59MM
Minutes as 00 up to more than 59[MM]
Seconds as 0-59S
Seconds as 00-59SS
Seconds as 00 up to more than 59[SS]

Drop a comment, if you have any questions/suggestions.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Related


10 Comments

  1. Henning Henning May 30, 2019

    How can I change the default date or time format in a spreadsheet or even globally in LibreOffice Calc?

  2. Franck Franck March 4, 2020

    Thank you very much !

  3. Uwe Uwe March 11, 2020

    you wrote “… day represent a number counted from the date Jan 0, 1900.”

    That’s only correct, if you make a special setting in Tools -> Options -> LibreOffice Calc -> Calculate. The default is to count days since Dec 30, 1899 (which is day 0).

    • arindam arindam Post author | March 12, 2020

      thank you for the tip. The article is updated.

  4. Wretched Vermin Wretched Vermin April 23, 2020

    I’m amazed that LibreOffice doesn’t support fractional seconds. Apparently this is too complicated for them.

    • Michael Michael July 9, 2020

      For fractional seconds use “SS.00”

  5. STIVE STIVE July 20, 2020

    Probably I’m stupid, but I found all these replies incomprehensible. Here is how to convert a list of dates in the wrong format to the format you want.
    Suppose you have a list of dates in an excel like:-
    2019-10-11
    2021-06-21

    But, you want:
    OCT 2019
    JUN 2021
    TO CONVERT THE CELLS TO YOUR FORMAT , DO THE FOLLOWING:
    1. Copy all your list of dates into a text file and save it using a program like xed.
    2. Cancel all the dates in your excel file
    3. Right click over the column heading where you want to insert the list of dates.
    4. From the dropdown menu select “Format Cells”.
    5. Select the format you want. If the format you want doesn’t exist, you can create it as a user defined format. In my case, I selected Category Date and then Format Dec 1999 and the format code is MMM YYYY.
    Then , click on the OK button.
    6. Now, go back to your list of dates in your text file and select them all and copy them to your clipboard.
    7. Then, click on cell 2 of the column in your excel where you want to put the dates. then EDIT then PASTE THEN ok.
    8. THIS WILL GIVE YOU THE LIST OF DATES IMPORTED INTO YOUR EXCEL FILE IN THE REQUIRED FORMAT.

  6. Seth Seth September 20, 2020

    How do change a date such as 1/1/20 to Friday January First?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.