Count Cells Between Two Dates – Google Sheets

Spreadsheets often have dates as part of their data. This tutorial will help you determine how many of these dates are between two days. Grab a copy of the live spreadsheet to follow along.

Video Tutorial

See the same examples explained in a video.

Video thumbnail
YouTube Video about Counting Between Two Dates

Example 1 – Between Two Dates

Let’s see how many days in the list below are between February 17, 2022, and February 28, 2022.

We are going to cover three different methods to count these days. All of them will use the COUNTIFS function, which can take multiple conditions – one for the start date and one for the end date.

Dates Typed Directly into the Function

The quickest way to finish this formula is to type the dates directly into the following function.

Cells being counted when their values are between two dates
Dates Being Counted

=COUNTIFS(A1:A5,">2/17/2022",A1:A5,"<2/28/2022")

Using dates in formulas can be tricky, so this technique may not work for you. Notice the placement of the "s in the formula. They need to surround the < or > and the date value.

Using a Cell Reference for the Date

As shown in the video, you can also use dates from cells. If you had 2/17/2022 in cell C1 and 2/28/2022 in cell C2, you would write the formula as follows.

Counting the number of days between two dates with cell references
Using Cell References to Count Dates

=COUNTIFS(A1:A5,">"&C1,A1:A5,"<"&C2)

Notice that quotes do not enclose the cell references. The quotes are only around the operators. In addition, we use an ampersand (&) to join the operators to the cell references. The advantage of using cell references is that you can see the dates without showing the formula, and they are easier to change.

Using the DATE Function

The DATE function is the last and most reliable method of entering the dates.

Cells being counted when their values are between two dates
Dates Being Counted with the DATE Function

=COUNTIFS(A1:A5,">"&DATE(2022,2,17),A1:A5,"<"&DATE(2022,2,28))

Regardless of where you live, the DATE function always wants the inputs in YEAR, MONTH, DAY order.

Example 2 – Between and Including Two Dates

Lastly, if you want to count the days, including the start and end date, you must add the = operator to your formula.

Counting the number of days including the start and ends days
Dates Being Counted Including the Start and End Dates

=COUNTIFS(A1:A5,">=2/17/2022",A1:A5,"<=2/28/2022")

Live Examples in Sheets

Go to this spreadsheet for examples of the AVERAGE and AVERAGEA functions shown above that you can study and use anywhere you would like.

Conclusion

After going through these examples, you should be able to apply these techniques to the dates in your spreadsheet.

Leave a Comment