How to Enter Times Without Colons and Calculate Total Hours in Excel with WPS Office
Question details
The user needs to enter compact time entries such as 9a, 1015a, 5p, and 1130p without typing colons, convert them into real time values, and calculate total hours in a spreadsheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is recording times in a spreadsheet and wants a faster, consistent way to type time entries without colons.
- Observed behavior
- The compact text entries need to become valid time values so they can be summed as total hours.
Make sure the time entries in your workbook use a consistent format, such as 9a, 1015a, 5p, or 1130p. Open WPS Office Spreadsheets and confirm that the Formulas tab and Name Manager are available.
Create the TTIME LAMBDA Function to Convert Compact Times
Use this solution when you need to enter compact times without colons in WPS Office Spreadsheets and then calculate total hours.
This method stores the conversion logic in a reusable named LAMBDA called TTIME. It recognizes standard time values, one- to four-digit entries followed by A, P, AM, or PM, and plain numeric values such as 1015 or 2100.
In WPS Office Spreadsheets, click the Formulas tab and then click Name Manager. In the Name Manager dialog, select New to create a named formula.
In the Name box, enter TTIME. In the Refers to box, paste the following formula and press OK: =LAMBDA(input,IF(AND(ISNUMBER(input),IFERROR(input<1,0)),input,LET(inputTemp,SUBSTITUTE(input,"m",""),inputTime,IFERROR(--inputTemp,--LEFT(inputTemp,LEN(inputTemp)-1)),pm,0.5*(UPPER(RIGHT(inputTemp,1))="P"),output,IF(LEN(inputTime)>2,(LEFT(inputTime,LEN(inputTime)-2)+RIGHT(inputTime,2)/60)/24,inputTime/24)+pm,output)))
Click a blank cell, type =TTIME(A2), and press Enter. Right-click the cell, choose Format Cells, and select a Time format so the result displays as a time.
Use =SUM(B2:E2)*24 in a result cell and format it as General or Number to show total hours.
Enter Compact Times and Calculate Total Hours with WPS Office Spreadsheets
WPS Office Spreadsheets supports the same LAMBDA-based approach as Excel 365. Create TTIME once in Name Manager, type short time entries in a familiar spreadsheet layout, and get total hours without extra add-ins.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open your workbook, and go to the Formulas tab. Click Name Manager to access the named formula list.
- 2. Define the TTIME LAMBDA: Click New, enter TTIME in the Name field, paste the LAMBDA formula into Refers to, and click OK.
- 3. Enter times and sum total hours: Input compact times such as 1015a or 1130p, apply =TTIME(A2), and use =SUM(B2:E2)*24 to return the total hours.
Frequently Asked Questions
Which WPS Office version supports LAMBDA?
Use a recent WPS Office Spreadsheets version that includes LAMBDA support. Open the Formulas tab, click Name Manager, and if New accepts a named LAMBDA formula, the feature is available.
What time formats can TTIME handle?
TTIME handles entries such as 9a, 1015a, 5p, 1130p, 9am, 5pm, and numeric values like 1015 or 2100. Entries must not contain colons if you are using the compact format.
How do I calculate total hours from converted times?
After converting the entries with =TTIME(A2), use =SUM(B2:E2)*24 in a separate cell and format it as General or Number to show decimal hours.
Why does my TTIME result show a decimal or an error?
A decimal means the cell may be formatted as Number instead of Time; right-click the cell and select Format Cells > Time. An error usually means the input format is unsupported or the cell is empty.
Can I reuse TTIME in other workbooks?
The named LAMBDA is saved in the current workbook. You can recreate it in another workbook through Formulas > Name Manager, or copy a cell that uses TTIME if the name is also present.
