Function Problems

Excel: Convert 845a and 130p into Real Times

John WilsonJohn Wilson Nov 12, 2026 869 views

Question details

Convert compact text entries such as 845a and 130p into real spreadsheet time values for calculations.

Excel: Convert 845a and 130p into Real Times
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the compact time

Type a value such as 845a, 130p, 900a, or 1215p in cell A2. Keep the entry as text and do not add a colon.

2
Enter the conversion formula

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.

3
Fill the formula down

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.

4
Apply a time format

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.

5
Calculate the elapsed time

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.

Convert compact entries such as 845a and 130p with a spreadsheet formula
Overnight durations are supported: Using MOD(C2-B2,1) returns the correct positive duration when the end time falls after midnight, such as a 10:00 PM start and a 2:00 AM end.
WPS Spreadsheets solution

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. 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open an existing workbook or create a blank workbook.
  2. 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. 3. Convert the entries: Select B2 and enter the LET conversion formula, then press Enter and copy the formula down the column.
  4. 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. 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.
Convert compact a.m. and p.m. entries into usable spreadsheet time valuesCalculate elapsed time with [h]:mm formatting or return decimal hoursUse a familiar spreadsheet interface with formula and cell-formatting toolsOpen and save Microsoft Excel XLSX files with strong format compatibility
microsoft office alternative - wps office

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.