Google Sheets: How to Use an IF Function with Dates


You can use the following formulas to create an IF function with dates in Google Sheets:

Method 1: Create IF Function to Compare Date in Cell with Specific Date

=IF(A2<=DATEVALUE("10/15/2022"), "Yes", "No")

For this formula, if the value in cell A2 is equal to or before 10/15/2022, the function returns “Yes.” Otherwise it returns “No.”

Method 2: Create IF Function to Compare Dates in Two Cells

=IF(A2<=B2, "Yes", "No")

For this formula, if the date in cell A2 is equal to or before the date in cell B2, the function returns “Yes.” Otherwise it returns “No.”

The following examples show how to use each formula in practice.

Example 1: IF Function to Compare Date in Cell with Specific Date

Suppose we have a list of dates in Google Sheets that show when some task was done and we want to know if each task was done by 10/15/2022:

We can type the following formula into cell B2 to return “Yes” if the task in cell A2 was done before or on 10/15/2022 or to return “No” otherwise:

=IF(A2<=DATEVALUE("10/15/2022"), "Yes", "No")

We can then drag and fill this formula down to each remaining cell in column B:

Google Sheets IF function with dates

The formula returns either “Yes” or “No” depending on whether or not the date in column A is before or equal to 10/15/2022.

Note: The DATEVALUE function in Google Sheets converts a date stored as a string to a date that is recognizable by Google Sheets formulas.

Example 2: IF Function to Compare Dates in Two Cells

Suppose we have two columns of dates in Google Sheets that show when some task was done along with the task deadline:

We can type the following formula into cell C2 to return “Yes” if the task in cell A2 was done before or on the deadline in cell B2 or to return “No” if it wasn’t:

=IF(A2<=B2, "Yes", "No")

We can then drag and fill this formula down to each remaining cell in column C:

Google Sheets IF function to compare dates

The formula returns either “Yes” or “No” depending on whether or not the task in each row was done by the deadline.

Note: This formula assumes that both columns are formatted as dates (not text) in Google Sheets.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: Use an IF Function with Range of Values
Google Sheets: Check if Cell Contains Text from List
Google Sheets: How to Check if Multiple Cells are Equal

3 Replies to “Google Sheets: How to Use an IF Function with Dates”

  1. Hi! I have several dates used for column a then I copy and paste the same formula but it retains the date on column a. I want to know how do I copy and paste formula that varies.

  2. Hi. Thanks for helping. But i still have some problem that i still cannot find the formula.
    example:-
    i got [start date] AND [due date]
    1) let’s say if today month is MARCH and the item start date is MAY and the due date is JULY = will be “PENDING” because the item was not started yet based on MONTH.
    2) If today month is MARCH and the item start date is FEB and the due date is APRIL = will be “ONGOING” because the start and due dates are in the current month
    3)And if today month is MARCH and the item start date is JAN and the due date is FEB = will be “DONE” because the date already past.

    How can i find the formula ? Please reach me and help me during this.
    Thanks in advance!!

    1. Hi Fadzen…You can achieve this by using the `IF` function in Google Sheets to compare the start and due dates with the current date. Here’s a formula that should work for your described conditions:

      1. “PENDING” if the start date is after today.
      2. “ONGOING” if today’s date is between the start and due dates.
      3. “DONE” if the due date is before today.

      Here’s how you can write the formula:

      “`excel
      =IF(MONTH(TODAY()) < MONTH(A2), "PENDING", IF(AND(MONTH(TODAY()) >= MONTH(A2), MONTH(TODAY()) <= MONTH(B2)), "ONGOING", IF(MONTH(TODAY()) > MONTH(B2), “DONE”, “”)))
      “`

      Assuming:
      – `A2` is the cell with the start date.
      – `B2` is the cell with the due date.

      Here’s a detailed explanation:
      – `MONTH(TODAY())`: Gets the current month number.
      – `MONTH(A2)`: Gets the month number of the start date.
      – `MONTH(B2)`: Gets the month number of the due date.

      The formula checks:
      – If the current month is less than the start month, it returns “PENDING”.
      – If the current month is between the start and due months (inclusive), it returns “ONGOING”.
      – If the current month is greater than the due month, it returns “DONE”.

      ### Example
      For better clarity, let’s assume the following dates:
      – **Today’s date**: March 15, 2024
      – **Start date (A2)**: May 1, 2024
      – **Due date (B2)**: July 31, 2024

      The formula would return “PENDING” because the start date is after the current month.

      If you want to use exact dates rather than just the month, you would need a more detailed formula. Here’s a modified version to consider the exact dates:

      “`excel
      =IF(A2 > TODAY(), “PENDING”,
      IF(AND(TODAY() >= A2, TODAY() <= B2), "ONGOING", IF(TODAY() > B2, “DONE”, “”)))
      “`

      This version uses exact date comparisons rather than just the month.

Leave a Reply

Your email address will not be published. Required fields are marked *