Date and Time Formats in LibreOffice Calc

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 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 formating for each type.

Date Time Data
Date Time Data

Date are stored as numbers in Calc and each day represent a number counted from the date Jan 0, 1900.

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 Format list below. However, if you want to customize your own format you can use any of codes below and create your own custom format.

To create 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 an “user-defined” category is automatically created. Now you can modify your format according to your need. Refer format codes at the end of this article.

You can also rename the category = “User-defined” by clocking small note icon (blue arrow below) and click 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 which you can use in formatting cells.

Format Format Code
Month as 3. M
Month as 03. MM
Month as Jan-Dec MMM
Month as January-December MMMM
First letter of Name of Month MMMMM
Day as 2 D
Day as 02 DD
Day as Sun-Sat NN or DDD
Day as Sunday to Saturday NNN or DDDD
Day followed by comma, as in “Sunday,” NNNN
Year as 00-99 YY
Year as 1900-2078 YYYY
Calendar week WW
Quarterly as Q1 to Q4 Q
Quarterly as 1st quarter to 4th quarter QQ
Era on the Japanese Gengou calendar, single character (possible values are: M, T, S, H) G
Era, abbreviation GG
Era, full name GGG
Number of the year within an era, without a leading zero for single-digit years E
Number of the year within an era, with a leading zero for single-digit years EE or R
Era, full name and year RR or GGGEE

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

Locale Year Month Day Hour Day Of Week Era
English – en
and all not listed locales
Y M D H N G
German – de J   T      
Netherlands – nl J     U    
French – fr A   J   O  
Italian – it A   G   O X
Portuguese – pt A       O  
Spanish – es A       O  
Danish – da       T    
Norwegian – no, nb, nn       T    
Swedish – sv       T    
Finnish – fi V K P T  

 

List of Time format codes

To format time, use below format codes:

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

 

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

Leave a Reply

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

2 thoughts on “Date and Time Formats in LibreOffice Calc”