In this tutorial, you will learn about Date and Time formats and how they are represented in LibreOffice Calc.
Let’s explain it with examples of a working problem.
Table of Contents
Date and time formats in LibreOffice Calc
Date Formats
Open a blank Calc sheet and enter the items below – a date, a time, and a date-time.
"3/2/2018"
"6:00"
"3/2/2018 6:00"
Once you enter, Calc will 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. In the format list, you can use any format you want. The format code is displayed in the “format code” box in the below image.
Remember, the format changes based on the Language regional settings. For example, in India, the default date format is dd/mm/yy, whereas in the USA, it should be mm/dd/yy.
Convert the date to number
To convert the date to a number, simply choose Category=Number in the format cells window in Numbers tab, 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 that elapsed from Jan 0, 1900.
Time and Conversion
If you apply the same, i.e., format cells to numbers to the Time and Date Time, you can see the results below.
Time is represented on 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 (see at the bottom of this page under list format codes heading). 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 the Format Cells dialog. In that dialog, start editing the Format Code (red arrow below). Once you modify it, you can see that a “user-defined” category is automatically created. Now, you can modify your format according to your needs. Refer to the 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 clicking 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 noticing 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.
Using text() function
LibreOffice features a powerful function =TEXT() which can also be used to convert dates. Syntax is follows:
text(Number, Format)
Number: Numeric value
Format: The text representing the format (as listed below table)
Now, let’s use the same example, i.e. 43134 as a number, to convert it to a date. To do that, you should use:
=TEXT(43134,"DD/MM/YYYY")
Likewise, you can play around with various format codes to get the desired result.
Various complex examples
Here are some of the number format codes (to add in the format cells window) and Text function codes for your easy understanding. It also includes the text function values with output:
The date used, for example, 1/25/2024 Thursday, has a value of 45316.
| Number format | text function codes | Output |
| NNNNMMMM D, YYYY | =TEXT(45316,”NNNNMMMM D, YYYY”) | Thursday, January 25, 2024 |
| NN, MMM D, YY | =TEXT(45316,”NN, MMM D, YY”) | Thu, Jan 25, 24 |
| [NatNum12 D=ordinal-number]D MMM YYYY | =TEXT(45316,”[NatNum12 D=ordinal-number]D MMM YYYY”) | 25th Jan 2024 |
| [NatNum12 D=capitalize ordinal]D MMM YYYY | =TEXT(45316,”[NatNum12 D=capitalize ordinal]D MMM YYYY”) | Twenty-fifth Jan 2024 |
| [NatNum12 D=capitalize ordinal]NNNN MMMM D | =TEXT(45316,”[NatNum12 D=capitalize ordinal]NNNN MMMM D”) | Thursday, January Twenty-fifth |
| [NatNum12 YYYY=title year,D=capitalize ordinal]D” of “MMMM, YYYY | =TEXT(45316,'[NatNum12 YYYY=title year,D=capitalize ordinal]D” of “MMMM, YYYY’) | Twenty-fifth of January, Two Thousand Twenty-Four |
| QQ YYYY | =TEXT(45316,”QQ YYYY”) | 1st quarter 2024 |
| ww | =TEXT(45316,”ww”) | 4 |
| “week” ww | =TEXT(45316,'”week” ww’) | week 4 |
List of Date Format Codes
Below are the format codes for dates that 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 | |
| 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 the format codes below:
| 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.