Excel: Convert 845a and 130p into Real Times
Question details
Convert compact text entries such as 845a and 130p into real spreadsheet time values for calculations.

- Product
- Microsoft Excel 365, compatible with WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants to enter times without colons or AM/PM characters and calculate the duration between a start time and an end time.
- Observed behavior
- Entries such as 845a and 130p are stored as text instead of usable time values, preventing reliable duration calculations.
Place the compact entries in a dedicated input column, such as A2, and keep the converted results in a separate column. Confirm that each entry ends in a or p and that the minute portion is between 00 and 59.
Convert compact entries such as 845a and 130p with a spreadsheet formula
Use this Excel 365 or WPS Spreadsheets formula when times are entered as text with a final a or p suffix.
The formula separates the suffix from the numeric portion, interprets the first one or two digits as the hour, and converts the last two digits into minutes. It also handles 12a and 12p correctly.
After conversion, the result is a numeric time value that can be used in subtraction, sorting, filtering, and other spreadsheet calculations.
Type a value such as 845a, 130p, 900a, or 1215p in cell A2. Keep the entry as text and do not add a colon.
Select B2 and enter =LET(x,LOWER(TRIM(A2)),suffix,RIGHT(x,1),digits,--LEFT(x,LEN(x)-1),hours,QUOTIENT(digits,100),minutes,MOD(digits,100),TIME(MOD(hours,12)+IF(suffix="p",12,0),minutes,0)). Press Enter to return a real time value.
Select B2, use the fill handle to copy the formula down, or copy B2 and paste it into the remaining cells beside the compact entries.
Select the converted cells, open Format Cells with Ctrl+1, choose Custom, and enter h:mm AM/PM. Click OK to display results such as 8:45 AM and 1:30 PM.
If the converted start time is in B2 and the converted end time is in C2, enter =MOD(C2-B2,1) in D2. Format D2 with the custom format [h]:mm, or multiply the result by 24 to return decimal hours such as 4.25.

Use separate helper formulas when LET is unavailable
Use this approach in older Excel versions or spreadsheet apps that do not support the LET function.
Convert Compact Text to Calculable Times in WPS Spreadsheets
WPS Spreadsheets can convert entries such as 845a and 130p into real time values with a formula, apply readable time formats, and calculate regular or overnight durations without manually inserting colons.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open an existing workbook or create a blank workbook.
- 2. Enter the source values: Place entries such as 845a and 130p in column A, starting in A2, and reserve column B for converted results.
- 3. Convert the entries: Select B2 and enter the LET conversion formula, then press Enter and copy the formula down the column.
- 4. Format the times: Select the converted cells, open the Home tab's Number Format controls or Format Cells, and apply h:mm AM/PM.
- 5. Calculate a duration: Use =MOD(C2-B2,1) for converted start and end times, then format the result as [h]:mm or multiply it by 24 for decimal hours.

Frequently Asked Questions
What formula converts 845a into a real spreadsheet time?
If 845a is in A2, enter =LET(x,LOWER(TRIM(A2)),suffix,RIGHT(x,1),digits,--LEFT(x,LEN(x)-1),hours,QUOTIENT(digits,100),minutes,MOD(digits,100),TIME(MOD(hours,12)+IF(suffix="p",12,0),minutes,0)). Then format the result as h:mm AM/PM.
How should I calculate the duration between two converted times?
If the converted start time is in B2 and the converted end time is in C2, enter =MOD(C2-B2,1). Format the result as [h]:mm. Multiply the result by 24 if you need decimal hours.
Will the formula work for times after midnight?
Yes. MOD(C2-B2,1) returns a positive duration when the end time is earlier than the start time because the period crosses midnight. For example, 10p to 2a returns 4:00.
How do I display 130p as 1:30 PM instead of a decimal?
Select the converted result, press Ctrl+1 to open Format Cells, choose Custom, enter h:mm AM/PM, and select OK. The underlying value remains a numeric time for calculations.
What should I do if LET is not supported in my spreadsheet app?
Use helper columns to extract the suffix and digits, then build the time with =TIME(MOD(QUOTIENT(C2,100),12)+IF(B2="p",12,0),MOD(C2,100),0). This works in older spreadsheet versions that support standard functions.




