Fix WPS Spreadsheet AVERAGE #DIV/0! for DATEDIF Results Displayed as Days
Question details
The user needs to average DATEDIF day counts that are displayed with the word “days” without converting the calculation results to text.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- DATEDIF calculates the number of days between start and end dates in a column, and the user wants to calculate the average of those day counts.
- Observed behavior
- When the DATEDIF result is joined to the text “days,” the cells become text. AVERAGE ignores those values and returns #DIV/0! when no numeric cells remain.
Check that the start and end date cells contain valid spreadsheet dates and that each end date is later than or equal to its start date. Use a custom number format to show the unit instead of adding text with an ampersand or concatenation formula.
Keep DATEDIF results numeric and format them as days
Use this method when you need DATEDIF values to remain available for AVERAGE and other calculations while still displaying the word “days.”
Concatenating a DATEDIF result with text, such as =DATEDIF(B2,F2,"D")&" days", changes the result into text. Return only the numeric day count in the formula, then use a custom number format for the display label.
In the first result cell, such as N2, enter =DATEDIF(B2,F2,"D") and press Enter. Do not add &" days" to the formula.
Select N2 and drag the fill handle down through the required rows, such as N124, to calculate the day count for each record.
Select N2:N124, right-click, choose Format Cells, open Number or Custom, and enter the custom format 0 "days". The cells will display values such as 15 days while remaining numeric.
In the target cell, enter =AVERAGE(N2:N124) and press Enter. WPS Spreadsheet can now calculate the average because the DATEDIF results are numbers.
Exclude blank or incomplete date rows from the day-count column
Use this option when some rows do not yet contain both dates and should not produce incomplete DATEDIF calculations.
Calculate and Display Average DATEDIF Days in WPS Spreadsheet
WPS Spreadsheet lets you calculate date differences as numeric values, add a custom “days” display format, and average the results without text-conversion errors. Its familiar worksheet interface makes it easy to update formulas and formats across an entire column.
- 1. Open the worksheet in WPS Spreadsheet: Open the file in WPS Office and select the worksheet that contains the start dates, end dates, and DATEDIF results.
- 2. Replace text-based day formulas: Click the first result cell, such as N2, and enter =DATEDIF(B2,F2,"D") so the result remains a number.
- 3. Fill the formula through the data range: Drag the fill handle from N2 down to the final record, such as N124, to apply the numeric formula to each row.
- 4. Show the days label with formatting: Select the result cells, right-click, choose Format Cells, and apply the custom format 0 "days".
- 5. Calculate the average: Click the summary cell and enter =AVERAGE(N2:N124) to return the average number of days.
Frequently Asked Questions
Why does AVERAGE return #DIV/0! after I add “days” to a DATEDIF formula?
A formula such as =DATEDIF(B2,F2,"D")&" days" returns text, not a number. AVERAGE ignores text values, and it returns #DIV/0! if there are no numeric values left in the selected range.
How can I display “days” after a number without turning it into text?
Select the result cells, right-click, choose Format Cells, and apply the custom number format 0 "days". The displayed value includes the label, but the stored value remains numeric.
What formula should I use for blank start or end dates?
Use =IF(OR(B2="",F2=""),"",DATEDIF(B2,F2,"D")). It returns a blank cell until both date cells contain values, allowing AVERAGE to ignore incomplete rows.
Will AVERAGE ignore blank cells in the DATEDIF results range?
Yes. AVERAGE ignores blank cells and text cells, but it includes numeric DATEDIF results. This makes an IF formula that returns "" useful for incomplete date rows.
Can I average DATEDIF values if they use a custom number format?
Yes. A custom format such as 0 "days" affects only the display. The underlying DATEDIF results remain numbers, so =AVERAGE(N2:N124) works normally.
