Find if Date is Between Dates in Google Sheets (An Easy Guide)

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.

dataset to find if date is between dates in google sheets

Our formula to check whether the date is between the two dates:

=(D3>=B3)*(D3<=C3)

Or we can use the AND function to achieve similar results:

=AND(D3>=B3,D3<=C3)

finding if date is between dates in google sheets using the and function

We round it off with an IF function to deliver our result in a more meaningful way.

=IF(AND(D3>=B3,D3<=C3),"YES","NO")

adding if function to the formula to present a meaningful message

Let’s see the formula at work with more dates.

find if multiple dates are between dates in google sheets

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:

table of start dates and end dates

Our formula to count occurrences if the date is between two dates:

=ArrayFormula(SUM(IF((F2>=B3:B)*(F2<=C3:C),1,0)))

counting occurrences of dates in between dates in google sheets

Formula Breakdown:

  • (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.

Read More: How to Use ARRAYFORMULA with IF Function in Google Sheets


Similar Readings


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.

dataset with date an units delivered

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:

=SUMIFS(C3:C,B3:B,">="&F3,B3:B,"<="&F4)

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.

Read More: Filter Values that Contains Multiple Text Criteria in Google Sheets (2 Easy Ways)


Final Words

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.


Related Articles 

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

2 Comments
  1. Hi,
    I have a date table:

    Y Z AA
    2 School Holidays WA
    3 Holiday Start Finish
    4 Term 1 (Autumn) 29 Mar 2024 14 Apr 2024
    5 Term 2 (Winter) 29 Jun 2024 14 Jul 2024
    6 Term 3 (Spring) 23 Sep 2023 8 Oct 2023
    7 Term 4 (Summer) 15 Dec 2023 30 Jan 2024

    And dates in A3:A2517

    I want a “SH” in O3:O2517 if a date in A3:A2517 matches any dates Z4 to AA4, Z5 to AA5, Z6 to AA6, Z7 to AA7

    This works fine for me,
    =ARRAYFORMULA(SUM(IF(($A3>=$Z$4:$Z$7)*($A3=$Z$4:$Z$7)*($A3=$Z$4:$Z$7)*($A3:$A50<=$AA$4:$AA$7), "SH", "" ))

    Which got me:
    <Formula here, in row 3,

    SH. < this is row 6
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A

    Note, there should be no hit in A3, and hits in A4:A8

    Um, help?

    • Hello,
      Thanks for your comment.
      You can use this formula instead which returns SH in cells O3:O2517 if any dates from cells A3:A2517 fall between dates in cells Z4:AA7. Don’t forget to check if all the dates are in Date format.

      =ARRAYFORMULA(IF(LEN(A3:A2517),IF((A3:A2517 >= Z4) * (A3:A2517 <= AA4) +(A3:A2517 >= Z5) * (A3:A2517 <= AA5) +(A3:A2517 >= Z6) * (A3:A2517 <= AA6) +(A3:A2517 >= Z7) *(A3:A2517 <= AA7) > 0, “SH”, “” ), “”))
      Using ARRAYFORMULA with IF and LEN functions

      Regards
      Sajid Ahmed
      OfficeWheel

Leave a reply

OfficeWheel
Logo