Dates in Google Sheets are stored as integers, making it quite easy to use them for various calculations. And one of these calculations could be to find if a date is between dates in Google Sheets.
But that doesn’t stop there. We can use this idea and implement them in various scenarios, which we will discuss in this article.
Let’s get started.
How do you check if a date is between two dates in Google Sheets?
The key is comparison. We compare our date with two others, namely the start date and the end date. For a date to be in between two dates, it has to be:
- Greater or more recent than the start date.
- Less than or older than the end date.
For example, we have in the following dataset a Start Date and an End Date. We will check whether our inputted date is in between these.
Our formula to check whether the date is between the two dates:
Or we can use the AND function to achieve similar results:
We round it off with an IF function to deliver our result in a more meaningful way.
Let’s see the formula at work with more dates.
Note: We have used absolute cell reference ($) for the start and end dates to make the formula always point to them as it moves down the column.
Examples of the Criteria: If a Date is Between Dates in Google Sheets
Calculate the Occurrences of Date between two Columns
Counting occurrences takes the idea of finding a date between two dates and takes it a step further. It has many practical uses.
But we will be keeping things simple with the following worksheet:
Our formula to count occurrences if the date is between two dates:
- (F2>=B3:B)*(F2<=C3:C): Our condition is to determine whether the date (cell F3) is in the range. The start date is in column B and the end date is in column C. We can also take advantage of the AND function here as we have seen previously.
- IF(condition, 1, 0): The function here returns 1 if the condition is TRUE (date is found), and 0 otherwise. This number will be added by SUM.
- SUM: Adds all the occurrences of the date if it is within range in the columns.
- ARRAYFORMULA: On their own, the SUM and IF functions can return only one value, the first value. With ARRAYFORMULA, all the rows in the start and end date columns will be counted.
Note: You can automatically apply ARRAYFORMULA after typing the base formula, SUM(IF((F2>=B3:B)*(F2<=C3:C),1,0)) and pressing CTRL+SHIFT+ENTER instead of just ENTER.
Sum Values if Date is Between Two Dates
For this scenario, we have a bunch of dates and the number of products delivered, in units, at those dates.
We know that at a professional level, this data would be better organized. But we have presented our dataset this way to show you that no matter the organization of the data, our formula will work.
What we are trying to do is sum all the units delivered within the given date range. Our formula:
The crux of our formula is the SUMIFS function. The function allows us to sum a range of cells according to multiple criteria. The multiple criteria for this example would be the “greater than start date” and “less than end date” to find a date in between from the Date column.
Once the criteria are met, the function will add the corresponding values from the sum range.
We have also kept our sum range and criteria ranges dynamic and open-ended (C3:C) so that we can add more values to the table in the future.
That concludes all the ways we can use a date if it is between dates in Google Sheets. We hope that our discussion comes in handy and that you now have a clearer idea of these uses.
Please feel free to leave any queries or advice you might have in the comments section below.