LibreOffice Calc · Number, date and currency formatting · Beginner
Format Numbers, Dates and Durations in LibreOffice Calc
Format currency, percentages, dates and durations in Calc while checking the stored numbers, locale, and precision settings.
Documentation-qualified; native workflow and save/reopen checks pending · Editorial standards
A number format changes how you read a value. It does not perform an exchange-rate conversion, turn an identifier into a quantity, or repair an imported text date. This exercise keeps the input and its meaning separate from its appearance.
Set up controlled numeric inputs
Use a new ODS workbook. The examples use English function names and semicolon separators; adjust the argument separator if your Calc configuration differs. Arithmetic expressions avoid relying on a decimal separator during entry.
Enter the following in A1:B6:
| Cell in column A | Label | Entry in column B |
|---|---|---|
| A1 | Measure | B1: Value |
| A2 | Invoice | B2: =12345/10 |
| A3 | Rate | B3: =75/1000 |
| A4 | Due date | B4: =DATE(2026;10;14) |
| A5 | Elapsed time | B5: =27/24+30/1440 |
| A6 | Precision test | B6: =1234567/1000 |
Before comparing precision, check Tools → Options → LibreOffice Calc → Calculate and ensure Precision as shown is off for this practice workbook. On macOS, use LibreOffice Preferences instead of Tools Options. Do not change this setting casually in a production model: it affects calculation behavior. The Calculate help explains the distinction.
The directions are based on Calc Guide 26.2 (February 2026) and online Help labeled 26.8 checked 19 October 2026. Independent arithmetic supports the expected results below; native display and persistence tests have not been run.
Choose the currency and locale explicitly
Select B2 and choose Format → Cells → Numbers. Select Currency, an explicit English (USA) locale (called Language in some editions), and the intended US-dollar currency entry. Use two decimal places and inspect the preview before applying. The value should be presented as 1,234.50 US dollars, with the selected format determining symbol placement.
A euro symbol applied to the same cell would still describe the number 1234.5; it would not convert dollars to euros. Choose the currency separately from separators and locale. Numbers help and Cells in Currency Format describe these controls.
In C2 enter =B2*2, then set C2 to Number with two decimals. Expect 2469.00 under English (USA) formatting.
Format the rate and date
For B3, use Percentage, one decimal place: 7.5%. In C3, enter =B3*100; with ordinary Number formatting, expect 7.5. If you had entered the numeric value 7.5 before applying Percentage, it would display 750%, a hundredfold error.
For B4, select English (USA) and enter exactly YYYY-MM-DD in the Format code field. Expect 2026-10-14. This starts with a DATE formula, so the exercise does not depend on Calc recognizing typed ISO-looking text. In C4 enter =B4+1, select English (USA), and apply the same exact YYYY-MM-DD format code: 2026-10-15.
Display a duration longer than a day
Select B5. In the Numbers tab set English (USA) and enter [HH]:MM as the format code. Expect 27:30. The brackets preserve hours beyond 23; HH:MM would instead show the time-of-day component 03:30. In C5 enter =B5*24 and format as Number with one decimal: 27.5 hours. The stored quantity is a fraction of a day, not the decimal number 27.30. See Number Format Codes.
Change B5 temporarily to =30/24. With the elapsed format expect 30:00, and C5 should be 30.0. Restore the original expression and verify 27:30 and 27.5.
Prove that display precision is not the calculation
Format B6 as Number with two decimal places: 1234.57. In C6 enter =B6*1000; format it as Number with zero decimals. Expect 1234567 with Precision as shown off. Merely showing two decimal places has not replaced the original 1234.567 with 1234.57.
If a calculation really needs two-decimal rounding, use an explicit formula such as =ROUND(B6;2) in D6; multiplied by 1000, that rounded result is 1234570. Decide the rounding rule from the task, not from the current column display.
Troubleshooting and recovery
- Dates or amounts stay as text: number formatting cannot fix the type. See text-to-number repair and text-to-date repair.
- Unexpected separators: inspect the cell’s Numbers locale and input locale; do not insert punctuation into the number merely to imitate a display.
- Hashes instead of a readable value: widen the column and inspect the underlying value and format.
- Identifiers lose zeroes: use the separate leading-zero CSV workflow.
Restore the six original entries, save, close, and reopen the ODS. Recheck each displayed meaning, C2:C6, the elapsed format code, and Precision as shown. CSV is not a format-preserving substitute for this exercise.