How to Average Call Durations in WPS Office Excel Without Returning Zero
Question details
The user needs to calculate and average call durations in a spreadsheet while keeping duration cells blank until an end time is entered.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A call log contains start and end times; the user wants the duration to stay blank until the end time exists and the average to ignore blank or zero values.
- Observed behavior
- Duration formulas can return zero or text values, which causes the average calculation to be incorrect.
Confirm that the call start and end cells contain real time values, such as 9:30 AM, rather than text. Decide which column will hold the duration so you can apply the custom time format to all relevant cells at once.
Use IF with a custom [h]:mm format to keep duration blank until end time
Use this when you want the duration cell to remain blank until an end time is entered and the average to ignore those blank cells.
Click the cell where the first call duration should appear, for example K4.
Type =IF(J4="","",J4-I4) and press Enter. If end time J4 is empty, the cell remains blank; otherwise it subtracts start time I4 from J4.
With the duration cell selected, press Ctrl+1, open the Number tab, choose Custom, and enter [h]:mm or [h]:mm:ss in the Type box.
In the average cell, enter =AVERAGEIF(K4:K100,"<>0") or =AVERAGE(K4:K100) and format that cell with the same [h]:mm custom format.
![Use IF with a custom [h]:mm format to keep duration blank until end time](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-13/6a8b43ca74c407918d58fbc209ad71b5.png)
Force a numeric duration with --TEXT to avoid text or zero issues
Use this if your existing duration formulas return text or zero and AVERAGEIF is not calculating correctly.
Calculate Average Call Duration Without Zero in WPS Office Spreadsheets
WPS Office Spreadsheets can directly calculate call durations with standard Excel formulas, keep cells blank until end time, and average only numeric results while maintaining full .xlsx compatibility.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel file that contains call start and end times, or create a new spreadsheet.
- 2. Add the duration formula: In the first duration cell, enter =IF(J4="","",J4-I4) and press Enter so the cell remains blank until J4 has an end time.
- 3. Format duration cells: Select the duration cells, press Ctrl+1, click Custom, and enter [h]:mm or [h]:mm:ss to show elapsed time correctly.
- 4. Insert the average formula: In the average cell, enter =AVERAGEIF(K4:K100,"<>0") and format the result with the same custom time format.
- 5. Save or export: Save the file as .xlsx to maintain compatibility with Microsoft Excel.
Frequently Asked Questions
Why does my call duration formula show 0:00 instead of blank?
This usually happens when the formula subtracts an empty end time as zero. Use =IF(J4="","",J4-I4) so the cell stays blank until J4 has an end time, then format the result as [h]:mm.
How do I display total minutes and seconds for call duration?
Select the duration cells, press Ctrl+1, choose Custom, and enter [mm]:ss. Use [h]:mm:ss if the calls can exceed 60 minutes and you want hours, minutes, and seconds.
Will AVERAGEIF ignore blank duration cells?
Yes. AVERAGEIF only includes cells that meet the condition. For numeric durations use =AVERAGEIF(K4:K100,"<>0"), or use AVERAGE if blank cells are truly empty.
Does WPS Office support the same time formulas as Excel?
Yes. WPS Office Spreadsheets supports IF, AVERAGEIF, custom number formats, and Excel-compatible .xlsx files, so the same duration formulas work.
What should I do if my times are stored as text?
Convert them to real time values by using TIMEVALUE or by retyping the times as 9:30 AM style entries. Text values cannot be correctly subtracted or averaged.
