Spreadsheets often contain dates as part of their data. In this tutorial, I will show you how to determine the number of dates that fall between two specific days using Google Sheets. To follow along, you can download a copy of the live spreadsheet.
Video Tutorial
If you prefer to learn through videos, check out the tutorial below where I explain the same examples.
Example 1 – Between Two Dates
Let’s start by finding out how many days in a given list fall between February 17, 2022, and February 28, 2022.
There are three different methods we can use to count these days, all of which involve the COUNTIFS function. This function allows us to specify multiple conditions, one for the start date and one for the end date.
Method 1: Directly entering dates in the function
The quickest way to accomplish this is by typing the dates directly into the function, like this:
=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 everyone. Pay attention to the placement of the quotation marks in the formula. They should surround the < or > sign and the date value.
Method 2: Using a cell reference for the date
Alternatively, as I mentioned in the video, you can also use dates from other cells. If you have February 17, 2022, in cell C1 and February 28, 2022, in cell C2, you would write the formula like this:
=COUNTIFS(A1:A5, ">"&C1, A1:A5, "<"&C2)Notice that the quotation marks do not surround the cell references. The quotation marks are only used around the operators. Additionally, we use an ampersand (&) to concatenate the operators with the cell references. The advantage of using cell references is that you can see the dates without displaying the formula, and they are easier to modify.
Method 3: Using the DATE function
The DATE function is the most reliable method for entering dates. Here is how you can use it:
=COUNTIFS(A1:A5, ">"&DATE(2022, 2, 17), A1:A5, "<"&DATE(2022, 2, 28))Regardless of where you live, the DATE function always expects the inputs in the order YEAR, MONTH, DAY.
Example 2 – Between Two Dates (inclusive)
If you want to count the days including both the start and end dates, you need to add the = operator to your formula. Here’s how it looks:
=COUNTIFS(A1:A5, ">=2/17/2022", A1:A5, "<=2/28/2022")Live Examples in Google Sheets
To practice the above examples and explore further, you can access this Google Sheets document where everything is set up for you.
Related Tutorials
If you found this tutorial helpful, you may also be interested in these related tutorials:
Now you have the tools to count cells between two dates in Google Sheets. Try it out in your own spreadsheets and let me know if you have any questions. Happy counting!
