Multiple Ways to Find The Difference Between Two Dates in Google Sheets

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Google Sheets offers a variety of functions to simplify your work with dates. One common task is determining whether a specific date falls between two other dates. Knowing how to achieve this will make your IF functions smooth when analyzing time-based data.

Today, we’ll discuss how to check the time between two dates, as well as apply the IF function with date comparisons in Google Sheets.

With a few simple steps, you can master the art of working with date ranges in Google Sheets. And as you become more proficient, you’ll find that these skills help you unlock the full potential of your data, allowing you to make more informed decisions.

Understanding Date Functions in Google Sheets

Google Sheets offers several powerful date functions that can help you manipulate and analyze dates in your data. In this section, we’ll explore three important date functions: DATEDIF, NETWORKDAYS, and DAYS. These functions can be used in various scenarios to compare dates, calculate the number of working days between dates, and more. Let’s dive in!

A Simple Method

Before you learn these Google Sheets functions, I want to show you a simple way to determine the number of days between two time periods. For this to work, you have to make sure that your dates are in the correct format. Otherwise, you may get an error.

If you have two dates in a proper format, as you see below, you could subtract the start date from the end date. By default, Google Sheets will return the number of days between the two.

simple method to find difference of dates

DATEDIF Function

The DATEDIF function calculates the difference between two dates in days, months, or years, to name a few. Its syntax is DATEDIF(start_date, end_date, unit), where start_date and end_date are the dates you want to compare, and the unit is the measurement unit (e.g., “D” for days, “M” for months, or “Y” for years). 

For example:

=DATEDIF(A2, B2, “D”)

This formula calculates the number of days between the dates in cells A2 and B2. But if we wanted to return the number of whole months of a calendar between the dates of our range, we can use the “YM” unit. To clarify, this looks at months in a twelve-month span and discounts any years.

DATEDIF function in Google Sheets

For reference, these are the units available to use in the DATEDIF function:

“Y” – Years

“M” – Whole months

“D” – Days

“MD” – Days after subtracting whole months

“YM” – Whole months after subtracting full years

“YD” – Days only if the difference is no more than one year.

NETWORKDAYS Function

The NETWORKDAYS function calculates the number of working days between two dates, excluding weekends and optionally public holidays. Its syntax is NETWORKDAYS(start_date, end_date, [holidays]), where start_date and end_date are the dates you want to compare, and [holidays] is an optional range of holiday dates. For example:

=NETWORKDAYS(A2, B2)

This formula calculates the number of working days between the dates in cells A2 and B2, excluding weekends. The predefined weekend days for this formula are Saturdays and Sundays.

If you want the function to filter out a holiday that isn’t as widely celebrated or even vacation days, you can use the optional argument. You can select the holidays from an existing range, type in the serial number representation of a date, or enter the dates using the DATE function.

For instance, let’s say you take a day off on January 6 for your anniversary every year. To include in the NETWORKDAYS function, it would look like this:

=NETWORKDAYS(A2,B2,DATE(2022,1,6))

Networkdays function

So, the result for our first row tells us there were 204 working days between the two dates.

DAYS Function

The DAYS function calculates the difference between two dates in just days, with no deductions. Its syntax is DAYS(end_date, start_date), where end_date and start_date are the dates you want to compare. For example:

=DAYS(B2, A2)

This formula calculates the number of days between the dates in cells B2 and A2. It’s a simpler alternative to the DATEDIF function when you only need the difference in days.

IF Function with Differences in Dates

While figuring out the number of days or months between dates is handy, including an IF function can take on a whole new meaning. You can use these nested functions to assign products, people, or anything to your data categories.

For example, let’s start a fantasy football team! We’ve had a log of applicants, but the only requirement is that you’re of legal age. So, let’s use the DATEDIF and IF functions and the TODAY function to narrow down our list.

Here’s the final formula.

=IF(DATEDIF(A2, TODAY(), “Y”)>18, “In Review”, “Too Young”)

This takes the applicant’s birthday and today’s date and returns the difference in years. Then, we ask if the result is greater than 18. If so, put the application in review; otherwise, the name on the list is too young to join.

if and datedif formula with today

This is just one way to apply the IF function to date differences. It’s simple, but the more complex formulas are made easier when you know how to find the exact separation in time you need!

Leave a Comment