To calculate the number of days ignoring blank cells in Google Sheets, none of the available functions have a supporting argument. For instance, the DAYS function does not have a return_zero_if_blank argument. The available functions, including DAYS, DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL, and DATEDIFF Query, do not cater to this specific requirement.
Introduction
One common calculation we often need in Spreadsheets is finding the number of days or network days worked by an employee from a start date to an end date. Usually, two cells are involved: one for the start date and another for the end date. However, if one of these cells is left empty, the calculation using the aforementioned functions would yield undesirable results.
Before delving into the formula to calculate the number of days ignoring blank cells in Google Sheets, let’s have a basic understanding of how these functions work.
Negative Days or Error Output
Let’s test the four functions in two different scenarios. In the first scenario, the end date is blank, resulting in negative days for all formulas except for DATEDIF, which returns #NUM!. This occurs because a blank cell is equivalent to the numeric value of 0, which represents the date 30/12/1899. To test this, you can manually enter the date 30/12/1899 in the blank cell and observe the same negative number of days and #NUM! output.
In the second scenario, we treat one of the cells as 30/12/1899, and the formulas return results accordingly. This emphasizes the importance of calculating the number of days while ignoring blank cells.
Formulas to Calculate Number of Days Ignoring Blank Cells in Google Sheets
Now let’s explore some solutions or workarounds. The first option is to check whether the cells are blank or not using the ISBLANK function or the “>” comparison operator. However, I don’t recommend using these options, as they may cause additional issues if the start or end date contains text instead of a date.
Instead, we can use the ISDATE or DATEVALUE combined with the four functions mentioned above to avoid negative days or errors in the calculation of the number of days or date differences. Although both ISDATE and DATEVALUE work well for this purpose, the latter has an advantage in array formulas.
ISDATE Non-Array Formula to Skip Blank Cells in Days or Network Days Calculations
The ISDATE function returns FALSE if the cell value is blank, a string, or any number. We can use IF with ISDATE to skip blank cells in the number of days calculation. Here’s the syntax:
=IF(AND(ISDATE(start_date), ISDATE(end_date)), days_calculation_formula,)
In the example provided, the days calculation formulas (days_calculation_formula) are NETWORKDAYS, NETWORKDAYS.INTL, DAYS, and DATEDIF. To calculate the number of days or network days while ignoring blank cells, we can use the following formulas:
- NETWORKDAYS:
=IF(AND(ISDATE(start_date), ISDATE(end_date)), NETWORKDAYS(start_date, end_date),) - NETWORKDAYS.INTL:
=IF(AND(ISDATE(start_date), ISDATE(end_date)), NETWORKDAYS.INTL(start_date, end_date, 1),) - DAYS:
=IF(AND(ISDATE(start_date), ISDATE(end_date)), DAYS(end_date, start_date),) - DATEDIF:
=IF(AND(ISDATE(start_date), ISDATE(end_date)), DATEDIF(start_date, end_date, "D"),)
DATEVALUE Array Formula to Calculate Number of Days or Network Days Ignoring Blank Cells
For array formulas, we need to make some changes to the non-array formulas mentioned above. Instead of using the logical AND operator, which doesn’t support arrays, we can use the DATEVALUE function in combination with other operators. Here’s an example using the non-array formula in cell C2:
=ArrayFormula(IFERROR(IF(DATEVALUE(A2:A8)*DATEVALUE(B2:B8)>0, NETWORKDAYS(A2:A8, B2:B8),)))
By replacing the logical AND with DATEVALUE(A2:A8)*DATEVALUE(B2:B8)>0 and using the formula as an array formula, we can calculate the number of days while ignoring blank cells. The same formula applies to cells D2, E2, and F2.
These formulas can be extended to an entire column range in Google Sheets by replacing the specific cell ranges (e.g., A2:A8, B2:B8) with A2:A and B2:B.
That’s all there is to it! Now you can easily calculate the number of days ignoring blank cells in Google Sheets using these formulas. Happy calculating!
