How to Convert 1300 to 13:00 in Excel with WPS Spreadsheet
Question details
The user needs to convert four-digit entries such as 1300 into readable 13:00 time values.
- Product
- Microsoft Excel or WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains four-digit time entries without a colon, and the user wants them converted or displayed as standard hours and minutes.
- Observed behavior
- Entries such as 1300 do not automatically display as 13:00.
Confirm that each source entry contains four digits representing hours and minutes, such as 1300 for 13:00. Choose an empty column for the converted results so the original entries remain available for comparison.
Convert 1300 to a real 13:00 time value with a formula
Use this method when the converted result must work in time calculations, sorting, and other spreadsheet functions.
The TIME function creates a genuine spreadsheet time value from the first two digits as hours and the last two digits as minutes. After entering the formula, apply an hh:mm number format to show the result correctly.
In the cell beside the four-digit entry, select the formula bar or cell and enter =TIME(LEFT(A1,2),RIGHT(A1,2),0), replacing A1 with the cell containing 1300. Press Enter.
Select the formula cell, then drag its fill handle down the column or use Home > Fill > Down to convert the remaining entries.
Select the converted results, open Home > Number Format or Format Cells, choose Custom, enter hh:mm, and select OK.
Display four-digit entries as 13:00 without converting the value
Use this option when you only need the visual appearance and do not need the cells to behave as actual time values.
Convert four-digit entries into usable time values in WPS Spreadsheet
WPS Spreadsheet lets you turn entries such as 1300 into real 13:00 time values with the TIME, LEFT, and RIGHT functions, then apply an hh:mm format for clear display and reliable calculations.
- 1. Enter the formula: In a blank cell beside the source value, enter =TIME(LEFT(A1,2),RIGHT(A1,2),0), using the actual source-cell reference instead of A1.
- 2. Copy the formula: Use the fill handle or Home > Fill > Down to apply the formula to the rest of the entries.
- 3. Format the results: Select the converted cells, open Home > Number Format or Format Cells, choose Custom, enter hh:mm, and select OK.
Frequently Asked Questions
Will the formula work if 1300 is stored as text?
Yes. LEFT and RIGHT can extract the first and last two characters from a text entry such as "1300". Enter =TIME(LEFT(A1,2),RIGHT(A1,2),0) in a separate cell and format the result as hh:mm.
How can I convert 0930 to 09:30?
Use the same formula, =TIME(LEFT(A1,2),RIGHT(A1,2),0), then apply the hh:mm format. Preserve the leading zero in the source entry if it is stored as text; otherwise use a four-digit value such as 930 only with a formula designed to pad the hour.
Why does 1300 still behave like a number after I add a colon format?
A custom format such as 00\:00 changes only the appearance. To create a true time value for calculations, use the TIME formula and format its result as hh:mm.
Can I make 1300 convert automatically while typing?
Automatic conversion while typing generally requires a worksheet-change VBA macro in desktop Excel or a similar automation feature. If macros are unavailable, enter the value as 13:00 or use the formula method in a neighboring column.
