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.
[…] Source : libreofficehelp.com […]
[…] Source : libreofficehelp.com […]
How can I change the default date or time format in a spreadsheet or even globally in LibreOffice Calc?
Thank you very much !
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).
thank you for the tip. The article is updated.
I’m amazed that LibreOffice doesn’t support fractional seconds. Apparently this is too complicated for them.
For fractional seconds use “SS.00”
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.
Thank you !!! You figure it out !!
How do change a date such as 1/1/20 to Friday January First?
Use number format as [NatNum12 D=capitalize ordinal]NNNN MMMM D
See above example table
Can we use PHP date types, such as,
1st, 2nd, 3rd, 4th, 5th,… 20th, 21st, 22nd, 23rd, 24th, 25th,… 30th, 31st?
In my region, long dates are written as such,
Tuesday, 3rd November 2020.
The best that I can see is
Tuesday, 3 November, 2020,
and that is not acceptable.
You can use NatNum12 modifiers. See the updated article above under “examples” heading.
How do I format cells to represent running times? For example, if a cell has 13:37, it’s because something happened for 13 hours and 37 minutes, not 1:37pm. Thanks.
You can format cells to present 1:37pm to 13:37 only using HH and MM
How to format value 1.5 as “1 12” (1 day 12 hours)?
Another example 15.33333 as “15 8” (15 days 8 hours)?
The problem is you can’t do this using number format. Because 0.5 is a decimal number. It’s like half of 24 hours. But time is represented in a different way as minutes and seconds.
However, you can achieve what you are asking using the below formula where A1 contains 1.5
=TRUNC(A1) & ” ” & (A1-TRUNC(A1))*24
I have a list of bank transactions that are due, but how do I omit/change any Saturday or Sunday transactions to the following Monday?
In my bank transactions, I would like any Sat or Sun dates to be moved to the monday. How would I format the column?
Use the WEEKDAY function. Weekday function returns number of the day. For example, it returns 7 for Saturday and 1 for Sunday. So, if you have a date in cell B2, you can use a simple IF to convert it to Monday. Use nested if for Sunday logic with 1.
=IF(WEEKDAY(B2)=7,”Monday”,TEXT(B2,”dddd”))
Thank you.
I would like to know if there’s an easy way to convert dates that have 2021 into 2020. For instance, I am entering receipts from 2020 but the default (since it’s now 2021) is for my 03/25 to convert to 2021-03-25. Instead, I want 2020-03-25. I would love that to convert to 2020-03-25 without my having to write 2020/03/25 each time. Can I do that?
I thought one way might be to go ahead with them as 2021 and then switch to “General” format and subtract 365, then switch back to “Date.” But that seems clunky. Can’t I just have it autofill a chosen year if I’m entering the month and day?
No. Unfortunately, there is no option to turn off the current year as default when entering the date as MM/DD or DD/MM.
I would suggest you two options.
a) After you entered, do a FIND-REPLACE ALL by 2020 replacing all 2021.
b) change the year of the computer date where you are working. This might have other problems as well.
If I have a cell containing a date, formated to the eye as YMD, and I want to use this formatted string in a formula, instead of its native value, how would I do that?
No. You can not do that. The visual representation will not work in the formula. So, you should use TEXT function to convert the date to the actual value. For example –
if your date is 04/12/2021 (April 12) in cell B3 – then formatting it as YYMD would give you 21412.
To get that value, use
=TEXT(B3,"yy") & TEXT(B3,"m") & TEXT(B3,"dd")
which also give you 21412. Then apply formula there.
I have a column of start times and another of end times. In the third column I subtract the start from the end, and I want the display to be hours and minutes.
I used the custom format “H:MM” to get “1:43” with no leading zero, but I want a bit more control.
I want to add ‘h’ and ‘m’ to make clear what units are being shown, AND I want to exclude the hour altogether if it’s 0.
So it should display times like “1h 43m” or “17 m”.
Is that possible with a custom format string?
Try this:
Sample below:
So that requires a 4th column instead of just formatting the 3rd column cleverly. I suppose I could hide the 3rd column.
Not what I was hoping for, but it does solve my issue. Thanks!
This is lame. Many (most) parts of the world use YYYY MM DD as the default date and you can’t make this date the default format in LibreCalc. This is so 1990s.
Another Lame feature: Why calendar can not begin on 0. Jan 1900 as in excell. In such case you can directly display diference of dates in dd-hh:mm (00-12:33 for example). No one cares about not existing 0.1.1900 ancient day.
It would be useful if the same (or similar) techniques could be used to alter the format of dates in headers/footers, where there is no choice at the moment. Is this ever likely to be a feature?
I need to display a date if the machine date is today, and the machine time is after a specified time.
I’m using
=IF(AND(G5″3 PM”),””,G5)
but I only get #VALUE! when the display format is date.
How can I get this tp work?
Grace
You are missing a comma perhaps in AND
What function (or settings) should I use to convert a fully specified ISO 8601 datetime value including timezone into a fully specified ISO 8601 datetime value.
These two values I have:
2022-12-23T13:24:42+01:00
2022-12-23T19:02:02.222+01:00
They are stored or recognized as text, but I want fully specified ISO 8601 datetime values:
2022-12-23T13:24:42(.000)
2022-12-23T19:02:02.222
I can “convert” the values using Text to Columns using the Column Type Date (YMD).
12/23/22 01:24 PM
2022-12-23T19:02:02.222+01:00
I can turn these into what I want to show by applying cell formatting Date code:
YYYY-MM-DD HH:MM:SS.000
2022-12-23 13:24:42.010
2022-12-23T19:02:02.222+01:00
Now, there are two problems. It is clear that the second value was not converted to the Date type or recognized as such. But the first value also is wrong. It should show .000 at the end instead of .000
It seems the milliseconds are taken from the specified time zone, which of course should not happen. If time zone is not converted, it should just be ignored. Those are no milliseconds, not specified as such.
The second value is not converted at all. Is this because it is too long? If time zone is not converted and therefore not expected, is there a restriction on the length of the value to be converted?
What can or should I do (or use) to have these date values converted to get and show the correct and actual date and time.
“in the USA, it should be mm/dd/yy”
I’ve lived in the USA all my life. I haven’t seen anyone use 2-digit years in decades. I’ve never seen anyone write a leading zero for a 1-digit month or day.
Libreoffice’s format doesn’t follow ANSI, or NIST, or any of the US military date standards. Why on earth is this the default for the USA?
Also, even if it is the default for Libreoffice Calc, why isn’t it also the default for Libreoffice Base, or other modules?
None of this makes any sense.
OK, I’ve wasted an hour trying to figure this one out. I have a simple Calc sheet with:
Date : Item : Amount paid
Problem is that despite every date being entered using the numeric keypad as DD/MM/YYYY, What I’m getting is:
2024-06-07
22/07/2024
29/07/2024
2024-12-06
2024-06-11
What are the steps to select the entire column, and apply a single date format to all cells?
I’m baffled by this one. Deleting the entry and re-entering it doesn’t change anything, and I haven’t been able to find a format setting that seems to fix it.
Thank you!
I’m trying to migrate from PC and MS Office to Mac Sequoia. I’ve opened an xls file in Libre 24.8 and the first thing I want to do is to update a date cell. I use DD-MMM-YY, but when I type in 7/2/25 for 7 Feb 25 it displays in the cell as 2.Jul.2025 and in the long data field in the tool bar as 2025-07-02. I’ve tried (many times) using Format > Cells > Numbers/Date but it makes no difference. How hard can you make it for a user to format a cell!