Date and Time formats and how they are represented in LibreOffice Calc is explained in this tutorial.
Open a blank Calc sheet and enter below items – a date, a time and a date time.
Once you entered, Calc would reformat them as below using default Calc formating for each type.
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.
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
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.
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.
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”.
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.
|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|
|Quarterly as Q1 to Q4||Q|
|Quarterly as 1st quarter to 4th quarter|
|Era on the Japanese Gengou calendar, single character (possible values are: M, T, S, H)||G|
|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
|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:
|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.