Google sheets compare dates

In google sheets, you can use the comparison operators with dates if the dates are formatted properly. This means google sheets can recognize the input value as a date.

In this tutorial, we will see how google sheets compare dates.

If A1 and B1 cells contain two dates, below is the list of formulas to compare them.

Google sheets compare dates

Check if the dates are equal:

=A1=B1

If dates are not equal:

A1<>B1

Less than or Less than equal to:

=A1<B1
=A1<=B1

Greater than or greater than equal to:

=A1>B1
=A1>=B1

Use the IF formula to compare and output text:

=IF(A1<B1, "If TRUE then show this Text", "if False then show this text")

Multiple conditions:

=IFS(A1<B1,"A1 less than B1",A1>B1,"A1 greater than B1",true,"A1 equals to B1")

Table of content

• Format dates properly
• Check if cell is in date format
• Example of date comparison
• Google sheets compare dates using IFS formula
• Compare date with time

How to format dates properly

When you type a date in google sheets cell, it automatically detects if it’s a date. Here are some of the popular formats for entering dates:

MM/DD/YYYY -> 01/31/2022

YYYY/MM/DD -> 2022/01/31

Month D, YYYY -> Jan 31, 2022

D Month, YYYY -> 31 Jan, 2022

Month D -> Jan 31

D Month -> 31 Jan

How to check if the cell value is in date format?

To check if a number or text is a date format, use the below formula:

=ISDATE(A1)

The output will be True if it’s a date format else it will be False.

Example: Google sheets compare dates

Let’s see some examples of the date comparison in google sheets.

Are dates equal?

To compare if two dates are equal, you can use the equal to operator “=” (Note: only works for a date not for the date with time)

Is one date greater than or equal to another date?

To check if a date is greater than another date, use the “>” operator, and to check for greater than equals to, use the “>=” symbol.

Here is a demo:

Is one date less than or equal to another date?

To check for less than, use the “<” operator, and to check for less than equal to, use the “<=” operator.

Here is an example screenshot:

Google sheets compare dates with IFS formula

Let’s say you have a list of students in your class who have submitted their assignments but on different dates.

As a teacher, you want to know which students have submitted after the deadline.

Here is a demo of how you can do this with the IFS formula:

Compare dates with today’s date – TODAY() formula

In google sheets, the TODAY() function outputs the current date. Below is an example of comparing a date with today.

Compare two dates with the time

If you have dates with time in google sheet cells, then comparing them may produce the wrong results.

In such cases, you have to truncate the time from the date while comparing. Check the below example:

Days difference between two dates

To get the difference between two dates, you can subtract them from one another using the “-” operator.

Here is an example:

Months difference between the two dates

You can count the number of months or year difference between two dates using the DATEDIF() formula.

Here is an example to find month difference between two dates:

You can use the DATEDIF() formula to also get the year and days difference between two dates. Below are two screenshots, showing the same.

Google sheets compare dates not working – Solved

Even after you have followed all standard practices, you may face issues where date comparison is not working in your sheet. Below are some of the common issues users face and I have listed how to solve them.

Locale check

When you are working with a sheet and comparing dates, make sure you know the default region or locale settings. 

If your sheet is set to locale as the USA and you are trying to compare the date formats like 01.01.2022 with 01/01/2022, this would result as both are not equal.

This is because the 01.01.2022 date is formatted using a locale of Bulgaria. 

To check or change locale, go to File > Settings > General > Locale. From the drop-down select the country to set as the default locale for the current sheet.

Format check

When you are comparing dates in google sheets and you get unexpected results, always check if the value is an actual date.

To check if the value is a date or not, use the =ISDATE() formula.

If the value is not in date format, try to convert it to a valid date format and then perform the comparison.

Wrapping up

In this tutorial, we learned how google sheets compare dates. You can use these comparison methods in your worksheet to compare dates. Go ahead and try them now.

Appendix

[1] DATEDIF formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Logical functions:

Logical functions in google sheets
Learn more about Google sheets Formulas.
Error handling in google sheets