How to calculate the total time of call records with the WPS table

In the day-to-day work, we often need to from other information management system database to export the corresponding data for statistical calculation, such as from the online business hall to download the personal call Details records, from the attendance system to export staff to work time records and so on. Because of the design of the system, many times we find that the time data exported from these systems is not a real time format, but rather a time that is displayed in text format, resulting in the inability to calculate the corresponding sums directly in the WPS table. This will bring some trouble to the daily work. So, in the WPS table, how do we solve this problem?

When we download the details of our call records from the online business hall, we will find that the call time is in the text format, and we cannot calculate the summation directly in the WPS table, as shown in the following figure:

Solution:

We can combine the time function (left, MID and right) of the WPS table with the text function to convert the time data of the corresponding text format into the real time format data. To ensure data accuracy and ease of operation, we need to use column B as a secondary column to convert the time of each text format into a time format. We enter the formula in the B2:

=time (a2,2), MID (a2,4,2), right (a2,2)

Then move the mouse to the lower-right corner of the B2 cell, and when the mouse shape is "+", hold down the left mouse button, then pull down to the B11 cell and then release the left mouse button. At this point, the format displayed is as shown in the figure:

We then right-click the selected cell, click "Format Cells", select "Time" in the "category" option on the General tab, and then click "When-minute-second" format under "type", and then click "OK" button. At this point, we can draw the real time format, as shown in the following illustration:

Finally, enter the formula in cell B12: =sum (B2:B11), so that you can get the sum of the total time of the A-column call record. As shown in the figure:

Triple Recommendation: Click for free Download the latest version of WPS Office software "to see more computer tutorials

Function Analysis:

Since the data derived from the system is consistent in length and format, using this rule, we can use Left (a2,2) to intercept the contents of the 2-character length in the A2 cell to get the value of "hour", and then use Mid (a2,4,2), Intercepts the contents of the 2-character length from the 4th character in cell A2, obtains the value of "minutes", and then uses right (a2,2) to intercept the contents of the 2 character length on the right-hand side of the A2 cell, resulting in a value of "seconds". Finally, the time (hour, minute, second) function is used to convert to the temporal format.