Last updated on March 8, 2020
Date and Time formats and how they are represented in LibreOffice Calc is explained in this tutorial.
Open a blank Calc sheet and enter the below items – a date, a time and a date-time.
Once you entered, Calc would reformat them as below using default Calc formatting for each type.
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.
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 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.
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.
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 that 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.