Dates play a pretty important role in all sorts of data scenarios. When you’re working with spreadsheets, you’re bound to run into them. Now, getting the hang of how to organize and compare dates in Google Sheets (Finding Date Difference) is a skill that can seriously level up your efficiency, especially when you’re dealing with big batches of data.
In Google Sheets, there are a bunch of ways to put dates side by side and see how they measure up. You’ve got these operators, functions, and even formulas that come into play. They help you figure out if two dates are the same, if one’s bigger than the other, or which one comes first in line.
The cool part is that these techniques aren’t rocket science. You don’t need to be a spreadsheet wizard to get them.
In this post, we’re going to dive deep into all the different ways you can play the date comparison game in Google Sheets. By the end, you’ll be all set to tackle this like a pro.
Table of Contents
Overview of How To Compare Dates in Google Sheets
You can use different methods to compare dates, depending on what kind of info you need from your sheets.
One easy way is by using special symbols called “comparison operators.” These symbols help you figure out things like if two dates are the same, if one date is later than another, or if one is earlier. For example, ‘==’ checks if two dates are exactly the same, ‘>’ sees if one date is after the other, and ‘<‘ checks if one date is before the other.
When you’ve got more than one thing to consider, you can use these formulas called IF and IFS. They help you compare dates in a more detailed way. Also, you can work with the current date using this formula called TODAY(). Pretty handy when you need to compare with what’s happening right now.
Now, imagine your dates have not only days but also times. To compare these, you can use something called the Truncate function. It’s like making the times disappear temporarily so you can focus on the dates.
To get even fancier, you can figure out the gap between dates – how many days, months, or years are between them. This formula called DATEIF() does the trick. It shows you the exact number of days, months, or years, which is really helpful. Just make sure your dates are written in the right way so these tricks work like magic!
Using Comparison Operators to Compare Dates in Google Sheets
In Google Sheets, you’ve got this toolkit of operators that step in to help you compare dates in all sorts of ways.
Let’s break them down one by one.
Using the ‘Equal to’ (==) Operator to Compare Dates in Google Sheets
This one’s super handy. You can give it two dates, and it’ll tell you if they’re exactly the same.
To show you how it works, let’s take a peek at an example where we’re pitting the first date against the second one using the == operator.
Step 1: Select a cell for your result (Cell C2)
Step 2: Input this formula: =A2=B2
Once you’ve selected the cell where you want to compare the dates (cell C2), type the formula (=A2=B2), where A2 and B2 are the cells containing the dates you want to compare.
Step 3: Press enter
The result in cell C2 will appear as TRUE, as illustrated below.
Using Greater Than (>) Operator to Compare Two Dates in Google Sheets
The next in our date comparison arsenal is the Greater Than (>) operator. This operator steps up when you want to know which date is the big shot — the one that comes after the other.
Let’s dive into how this operator works its magic and opens up new possibilities for your spreadsheet skills.
Step 1: Select a cell for your result (Cell C2)
Step 2: Input the formula: =A2>B2
Here, A2 and B2 are the cells that contain the dates we want to compare.
Step 3: Press Enter
You will see your result in cell C2 as FALSE, as shown below.
Using Greater than or Equal to (>=) Operator to Compare Two Dates in Google Sheets
This operator steps up to the plate when you’re aiming to figure out not just which date comes after the other, but also if they’re equal.
Let’s delve into how this operator can compare dates in Google Sheets.
Step 1: Select a cell for your result (Cell C2)
Step 2: Insert the formula: =A2>=B2
Here A2 and B2 are the cells containing the Dates we want to compare, and >= is Greater than or Equal to the operator.
Step 3: Press Enter
The results in cell C2 will be displayed as TRUE, as illustrated below.
Using Less than (<) Operator to Compare Dates in Google Sheets
This operator comes into play when you want to determine which date holds the earlier spot in the timeline.
Let’s uncover how this operator can be used to compared dates.
Step 1: Select a cell for your result (Cell C2)
Step 2: Input the formula: =A2<B2
After highlighting cell C2, type the above formula, where A2 and B2 are the cells that contain the dates you want to compare.
Step 3: Hit Enter
As shown below, you will immediately see your result in cell C2 as FALSE.
Using Less than or Equal to (<=) Operator to Compare Dates in Google Sheets
This operator takes the stage when you need to determine not only which date precedes the other, but also if they’re equal.
The steps below will show you the way.
Step 1: Select a cell for your result (Cell C2)
Step 2: Enter this formula: =A2<=B2
With A2 and B2 as the cells containing the dates, we want to compare.
Step 3: Tap enter
You will see the result in cell C2 as FALSE, as illustrated below.
Using the TODAY() Formula to Compare Dates in Google Sheets
The TODAY() function is an inbuilt function in Google Sheets that recognizes the current date of your device in real-time, and you can compare it to any date in your Google Sheets.
Below is an example comparing dates using the TODAY() Formula.
Step 1: Select a cell for your result (Cell C2)
Step 2: Input the formula: =A7=TODAY()
A7 is the cell with the date we want to compare, = is the operator that will compare the two dates, and TODAY() is the function that will give the exact date of your device to be compared to.
Step 3: Hit Enter
You will see the result in cell B7 as TRUE because the date in cell A7 is the same as the current date, as shown below.
Using the Date Function to compare Dates in Google Sheets
When comparing dates in Google Sheets, you may need to use a specific date that is difficult to locate in your data set or even not in the actual spreadsheet, and this is where one can use the DATE function. This function will permit you to create a date with a specific individual year, month, and day to be used in comparison to the dates you desire.
The syntax of the DATE function is: =DATE (year, month, day). You can use the date function with other operators and formulas like greater than >, equal to =, or even the TODAY formula depending on the comparison type you want to make.
For example, we can use the Date Function as shown below to know whether the current date (7th August 2023) is greater than the first day of 2023.
Step 1: Select a cell for your result
Step 2: In cell B3, input =TODAY()>DATE(2023,1,1)
There TODAY() IS the function that will give the current date.
Step 3: Press Enter
A result of TRUE will appear, as demonstrated below. Your result will be true because 7th Aug 2023 is a later date than 1st Jan 2023.
Using the IFS Formula to Compare Dates in Google Sheets
To compare dates in Google Sheets using the IFS formula, you can utilize the DATEVALUE function to convert your data into a usable date format. The IFS formula will now allow you to perform multiple logical tests and return different results depending on the conditions met.
Here is an example of how you can use the IFS formula to compare dates in Google Sheets:
For instance, you have a list of students in a class who have submitted their projects but on different dates. If you want to know which student submitted after the deadline, you can now use the IFS Formula as demonstrated.
Step 1: Select a cell for your result (Cell D2)
Step 2: Input the formula below:
IFS(B2<C2,” Before Deadline,” B2=C2,” On Deadline,” true,” After Deadline”)
Where =IFS is the formula that will allow you to set multiple If conditions, B2 and C2 are the cells containing the dates to be compared,< and= are operators used to assess conditions, and Before, On, and After Deadline are the results to be displayed depending in the condition met.
Step 3: Press Enter
Immediately Before Deadline will be displayed in cell D2 meaning the condition met was B2<C2, and student Joel submitted their project before the deadline.
Step 4: Compare the entire dataset
Click and hold the dot at the bottom right corner of cell D2 and drag down until your desired range.
Comparing Two Dates With Time in Google Sheets
Comparing two dates with time values in the same cell may produce an unexpected result in google sheets.
However, google sheets allow you to use the TRUNC() formula, which will truncate the time value from the date and give you the correct expected result, as shown below.
Step 1: Select a cell for your result (Cell C3)
Step 2: Type in this formula: =TRUNC(A2)=TRUNC(B2)
Where TRUNC(A2) and TRUNC(B2) are the already truncated Dates without the time value.
Step 3: Hit Enter:
A result of TRUE will appear in cell C3.
Note: In cell C2 below, we have used the Equal to the operator by typing =A2=B2, and the result will be FALSE because the Operator will compare the dates and the time.
Finding Days Difference Between Dates in Google Sheets
To use the number of days between two dates in Google Sheets, you can Subtract these dates from each other using the “-” operator. Below is an example.
Step 1: Select a cell for your result (C2)
Step 2: Input the formula =B2-A2
Step 3: Hit Enter
The exact number of days between the two dates will appear as illustrated below. To apply the same formula for a range of data, just drag down the bottom line of cell C2 until your desired range.
Finding Days, Months & Years Difference Between Dates Using DATEDIF() Formula
Using the DATEDIF() formula, you can count the number of days, months, and years between two dates. Here is a clear illustration of this.
Step 1: Select a cell for your result
Always remember to start by selecting the cell where your result will be displayed. Using our examples below, you will select cell C2.
Step 2: Input the formulas below
- =DATEDIF(A2, B2, “M”) to compare the difference in Months between The dates in Cell A2 and B2.
- =DATEDIF(A2, B2, “Y”) to compare the difference in Years between The dates in Cell A2 and B2.
- or
- =DATEDIF(A2, B2, “D”)to compare the difference in Days between The dates in Cell A2 and B2.
Step 3: Press Enter,
You will see the exact number of Days, Months, Or Years displayed in cell C2 depending on the syntax you applied, as shown below.
To count the months between two dates, use the following syntax:
=DATEDIF(A2, B2, “M”).
To count the number of years between two dates, you will use the following syntax:
=DATEDIF(A2, B2, “Y”)
And finally, to count the number of days between two dates, you can use the syntax below:
=DATEDIF(A2, B2, “D”)
Conclusion
To sum up, comparing dates in Google Sheets is a straightforward process that can be done using various functions and operators.
To compare two dates, you can use logical operators such as greater than (>), less than (<), and equal to (=), among others, and by utilizing these tools, you can easily compare dates in Google Sheets and perform various calculations and even be able to format your google sheets accordingly based on the results.