Convert 24-hour time to 12-hour AM/PM in Excel

Change the number format (the time stays a time)

Excel stores a time as a fraction of a day: 14:30 is 0.604166..., because 14.5 hours is 60.4% of 24. Whether you see 14:30 or 2:30 PM is only the cell's number format. So the cleanest conversion is to change the format and leave the value alone.

This is the method to pick whenever the column holds real times. You can still subtract them, add them, sort them and use them in lookups. Check first: a real time sits on the right of the cell, and =ISNUMBER(B3) returns TRUE.

1. Select the times (B2:B7 in the shift sheet above).
2. Press Ctrl+1 to open Format Cells and click the Number tab.
3. Choose Custom and type h:mm AM/PM in the Type box. Use h:mm:ss AM/PM if you need seconds.
4. Click OK. 14:30 now shows as 2:30 PM, and the formula bar still shows the same time.

TEXT when the time goes into a sentence

When you join a time to text with &, Excel uses the stored value, not the format, so you would get "Shift starts at 0.604166666666667". Wrap the time in TEXT with the same format code to control how it looks.

TEXT returns text. That is fine for labels, emails and mail-merge columns, but do not use a TEXT column for maths: SUM skips it and comparisons treat it as words. Keep the real time in one column and build labels in another.

Midnight and noon: the two that confuse people

In 12-hour time, 00:30 is 12:30 AM and 12:00 is 12:00 PM. Excel gets both right, but people reading the sheet often do not, and staff who type times by hand get them wrong. Dev's 0:30 start is just after midnight, and Ema's 12:00 is midday.

When you type times, Excel reads 2:30 as 2:30 in the morning. Type 14:30 or 2:30 PM (with a space before PM) for the afternoon. If a column of afternoon shifts all show AM, they were typed without PM and are 12 hours out.

Back from 12-hour to 24-hour

For real times, use the custom format hh:mm (or h:mm). A time format without AM/PM displays in 24-hour style.

Booking systems often export 12-hour times as text, like the "Booked for" column. TIMEVALUE reads text such as "2:30 PM" and returns a real time. Format that result as hh:mm, or wrap it in TEXT if you need 24-hour text for another system.

Text times from exports: "14:30", "0645" and 645

Time clocks and HR exports rarely give you clean times. Column D came in as text: "14:30" with a colon works with TIMEVALUE or two minus signs (--D3). Format the result as h:mm AM/PM.

Times with no colon, like 1430, are the harder case. TIMEVALUE("1430") returns #VALUE!, because Excel does not see a time in it. And if the column was opened from a CSV as numbers, 0645 has already lost its leading zero and arrived as 645 (column C). Split the hundreds yourself: INT(C2/100) is the hours and MOD(C2,100) is the minutes. This works for 645, 1430 and 30, and for text like "0645" too.

Sort text times in the right order

Text sorts character by character, so "10:15 AM" comes before "2:30 PM" and "9:05 AM" lands last. Sorting the booking column with Data > Sort A to Z, or =SORT(H2:H7), gives 10:15 AM, 11:40 PM, 12:10 PM, 2:30 PM, 7:50 AM, 9:05 AM. The real fix is to convert the column to times first; then Sort Smallest to Largest works.

If you cannot change the column, sort by the converted values instead. In Excel 365 and 2021, SORTBY sorts one range by another, and the other range can be computed on the fly.