How to convert 12-hour text times to 24-hour
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Turn "3:45 PM" strings from exports into real 24-hour times.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =TEXT(TIMEVALUE(A2),"HH:MM") | Drop the TEXT wrapper to keep a real time value for math; format the cell as HH:MM instead. |
| Google Sheets | =TEXT(TIMEVALUE(A2),"HH:MM") | Identical. |
| LibreOffice Calc | =TEXT(TIMEVALUE(A2),"HH:MM") | Identical. |
How it works
TIMEVALUE parses a time written as text — including the AM/PM suffix — into the day-fraction spreadsheets use, and TEXT with an HH code renders it 24-hour style: "3:45 PM" → "15:45". For calculations (durations, sorting), skip the TEXT wrapper and just format the TIMEVALUE cell as HH:MM — keeping it numeric. If TIMEVALUE returns #VALUE!, the text has a nonbreaking space or stray characters; TRIM/SUBSTITUTE it clean first.
Verified, not just documented
We ran =TEXT(TIMEVALUE(A2),"HH:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 15:45 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 15:45 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
TEXT · TIMEVALUE — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to abbreviate large numbers as K and M
- How to add leading zeros to a number
- How to concatenate a date with text (without getting a number)
- How to convert a date to text
- How to convert minutes to hours and minutes