To calculate the number of days ignoring blank cells, none of the available functions in Google Sheets has any supporting argument.

For example, if you consider the DAYS function, the syntax DAYS(end_date, start_date) has no return_zero_if_blank argument.

When I say available functions, I meant to say DAYS, DATEDIF, NETWORKDAYS, and NETWORKDAYS.INTL. Other than these, there is the DATEDIFF Query function.

You May Like: How to Utilize Google Sheets Date Functions [Complete Guide]

Introduction

One of the calculations we most commonly require in Spreadsheets may find the number of days (or network days) worked by an employee from a start date to an end date.

So there must be two cells involved (for example, cells A2 and B2 respectively) – one for the start date and the other for the end date.

If one of them is empty, then the calculation using the above functions would return unwanted results.

Before going to the formula to calculate the number of days ignoring blank cells in Google Sheets, let’s get a basic idea of how the above functions work.

Negative Days or Error Output

Let’s test the above four functions (I am skipping the Query) in two different scenarios.

In the following example (please see the image below), cells A2 and B2 contain (or for entering) the start and end dates, respectively.

Scenario # 1

In the first scenario, the end date (B2) is blank. So the formulas except for DATEDIF in F2 return negative days, whereas the DATEDIF returns #NUM!

Cell B2 is blank means the numeric value of that cell is equal to 0, which is equal to the date 30/12/1899.

Related: How to Return Blank Instead of 30-12-1899 in Google Sheets Formulas.

To test that, you may please enter the date 30/12/1899 in cell B2. You will get the same negative number of days and #NUM! in C2:F2.

Scenario # 2

In scenario two, needless to say, the formulas treat A2 as 30/12/1899 and return the result accordingly.

Here comes the importance of calculating the number of days ignoring blank cells. There are different methods, but let’s only learn the most suitable ones.

Formulas to Calculate Number of Days Ignoring Blank Cells In Google Sheets

Let’s now think about solutions or workaround methods.

The first option is to test whether the cells in use are blank or not. For that, we can use the ISBLANK function or the > comparison operator.

But I don’t recommend both of them. The above may check non-blank cells but may cause additional issues if the start date or end date contains text instead of date.

We can use ISDATE or DATEVALUE combined with the above four functions to avoid negative days or errors in the number of days or date difference calculation.

Though both the functions (ISDATE and DATEVALUE) work well for our purpose, currently, the latter has an edge on the former.

I mean, the DATEVALUE will work seamlessly in array formulas. I’ll come to that at a later part of this tutorial.

ISDATE Non-Array Formula to Skip Blank Cells in Days or Network Days Calculations

The ISDATE function will return FALSE if a cell value is blank, a string, or any number.

So we can use IF with ISDATE as below to skip blank cells in the number of days calculation.

How?

Please follow the below syntax.

Syntax:

=if(and(isdate(start)date),isdate(end_date)),days_calculation_formula,)

In the above example (please refer to image # 1), there are four days calculation formulas (days_calculation_formula) in cells C2, D2, E2, and F2. They are;

C2: =NETWORKDAYS(A2,B2)

D2: =NETWORKDAYS.INTL(A2,B2,1)

E2: =days(B2,A2)

F2: =DATEDIF(A2,B2,"D")

To calculate the number of days or networking days ignoring blank cells in Google Sheets, we can use them like given below.

Note:- The following formulas are based on the syntax provided above.

C2: =if(and(isdate(A2),isdate(B2)),NETWORKDAYS(A2,B2),)

D2: =if(and(isdate(A2),isdate(B2)),NETWORKDAYS.INTL(A2,B2,1),)

E2: =if(and(isdate(A2),isdate(B2)),days(B2,A2),)

F2: =if(and(isdate(A2),isdate(B2)),DATEDIF(A2,B2,"D"),)

DATEVALUE Array Formula to Calculate Number of Days or Network Days Ignoring Blank Cells

Here also let’s start with the non-array formula first. That will be easy for most of you.

Unlike ISDATE, the DATEVALUE doesn’t return TRUE or FALSE. It outputs a date value if the cell in question has a date, else the error #VALUE.

So replacing ISDATE with DATEVALUE alone won’t solve our problem here.

What else do you require?

Here are the changes required to calculate the number of days ignoring blank cells using DATEVALUE with the formulas in question.

I’ll explain the changes in the formula in cell C2.

First, replace all the ISDATE in the formula with DATEVALUE. Then wrap the entire formula with IFERROR.

=iferror(if(and(datevalue(A2),datevalue(B2)),NETWORKDAYS(A2,B2),))

The same changes apply to the formulas in cells D2, E2, and F2.

If you are searching for an array formula for calculating the number of days ignoring blanks in Google Sheets, then the above formulas won’t help.

Because the logical AND operator used in the formula doesn’t support the array.

I have already explained How to Use IF, AND, OR in Array in Google Sheets. We can follow that here. Here is how.

If we take the above non-array formula in cell C2, we should make the following two changes.

1. Replace and(datevalue(A2),datevalue(B2)) with datevalue(A2:A8)*datevalue(B2:B8)>0

2. Use the formula as an Array Formula.

So the formula in cell C2 will be;

=ArrayFormula(iferror(if(datevalue(A2:A8)*datevalue(B2:B8)>0,NETWORKDAYS(A2:A8,B2:B8),)))

The formulas in cells D2, E2, and F2 follow the same.

The same formula will work in an entire column (infinite column range) in Google Sheets. For that, you may replace A2:A8 with A2:A and B2:B8 with B2:B.

That’s all. Enjoy!