Google Sheets offers a range of powerful functions to work with dates, making it easier to perform calculations involving time periods, workdays, and month-end dates. Among these, the DATEDIF, EOMONTH, and WORKDAY.INTL functions stand out as particularly useful tools for date-based calculations. Whether you’re calculating the number of days between two dates, adjusting dates based on work schedules, or finding the end of a month, these functions can help you manage time and dates more effectively in your spreadsheets.
In this Date Calculations Masterclass, we’ll take a deep dive into these three functions, explain their use cases, and provide practical examples to help you get the most out of them. Whether you’re a beginner looking to understand how date calculations work in Google Sheets or an experienced user refining your date-related formulas, this guide will help you master these essential functions.
What Are the DATEDIF, EOMONTH, and WORKDAY.INTL Functions?
Each of these functions serves a specific purpose related to date calculations:
- DATEDIF: Calculates the difference between two dates in various units (years, months, days).
- EOMONTH: Returns the last day of the month, a specified number of months before or after a given date.
- WORKDAY.INTL: Calculates the workday that is a specified number of days before or after a date, with customizable workweek definitions.
Now let’s explore each of these functions in more detail, with practical examples to demonstrate how they can be applied in real-world scenarios.
DATEDIF Function: Calculating the Difference Between Dates
The DATEDIF function is used to calculate the difference between two dates. It allows you to specify the unit of time you want to return (days, months, or years), making it a versatile tool for date-based calculations.
Syntax of DATEDIF
=DATEDIF(start_date, end_date, unit)
- start_date: The beginning date for the calculation.
- end_date: The ending date for the calculation.
- unit: The unit of time to return:
- “Y”: Years
- “M”: Months
- “D”: Days
- “MD”: Difference in days, ignoring months and years
- “YM”: Difference in months, ignoring years
- “YD”: Difference in days, ignoring years
Example 1: Calculate the Number of Days Between Two Dates
Let’s say you want to calculate the number of days between January 1, 2022, and December 31, 2022. You can use the following formula:
=DATEDIF("2022-01-01", "2022-12-31", "D")
This will return 364, the number of days between the two dates.
Example 2: Calculate the Age of a Person
If you want to calculate a person’s age in years based on their birthdate and today’s date, you can use DATEDIF as follows:
=DATEDIF(A1, TODAY(), "Y")
In this example, A1 contains the person’s birthdate, and TODAY() returns the current date. This formula will calculate the person’s age in years.
EOMONTH Function: Finding the End of the Month
The EOMONTH function helps you find the last day of the month that is a specified number of months before or after a given date. This can be useful for financial analysis, project timelines, or any situation where you need to know the end of a specific month.
Syntax of EOMONTH
=EOMONTH(start_date, months)
- start_date: The starting date.
- months: The number of months before (negative number) or after (positive number) the start date.
Example 3: Finding the Last Day of the Current Month
If you want to find the last day of the current month, you can use the following formula:
=EOMONTH(TODAY(), 0)
This will return the last day of the current month, based on today’s date.
Example 4: Finding the Last Day of Next Month
If you want to find the last day of the next month, use:
=EOMONTH(TODAY(), 1)
This formula will return the last day of the following month from the current date.
WORKDAY.INTL Function: Calculating Workdays with Custom Workweeks
The WORKDAY.INTL function calculates a workday that is a specified number of days before or after a given date, excluding weekends and optionally holidays. It allows you to define your custom workweek, which is useful for businesses that do not follow the standard Monday-to-Friday schedule.
Syntax of WORKDAY.INTL
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
- start_date: The starting date.
- days: The number of workdays to add or subtract (use negative numbers for past dates).
- weekend: An optional parameter that specifies which days of the week are weekends. For example:
- 1 = Sunday
- 2 = Monday
- 7 = Saturday and Sunday
- Custom workweek patterns can be specified using a 7-character string (e.g., “1111100” for a workweek from Monday to Friday).
- holidays: An optional range of holiday dates to exclude from the calculation.
Example 5: Calculate the Next Workday
If you want to calculate the next workday from a given date (e.g., January 1, 2022), you can use:
=WORKDAY.INTL("2022-01-01", 1)
This formula will return the next workday, which in this case would be January 3, 2022 (assuming a standard Monday-to-Friday workweek).
Example 6: Calculate a Workday with a Custom Workweek
If your company follows a different workweek, such as working Sunday to Thursday, you can specify this using a custom workweek pattern. For example:
=WORKDAY.INTL("2022-01-01", 1, "1111100")
This formula will calculate the next workday based on a Sunday-Thursday workweek, excluding Friday and Saturday as weekends.
Real-Life Scenarios: Applying These Functions in Your Workflow
Here’s a practical example where you could use all three of these functions in a project management scenario:
- Calculate the number of days remaining until a project deadline using
DATEDIFto measure the time difference between today’s date and the deadline. - Determine the last working day of the current month using
EOMONTHto manage timelines. - Calculate the next workday for a project deliverable using
WORKDAY.INTLto exclude weekends and holidays based on your company’s custom workweek.
Benefits of Using These Date Functions
- Efficiency: These functions streamline calculations involving dates, saving time when working with deadlines, timelines, and schedules.
- Accuracy: They ensure accurate date calculations, accounting for weekends, holidays, and custom workweeks.
- Customization: With WORKDAY.INTL and the ability to define custom workweeks, you can tailor your calculations to fit your business needs.
- Flexibility: Whether you need to calculate ages, workdays, or month-end dates, these functions are highly versatile and can be used in a variety of scenarios.
Quick Reference Cheat Sheet for Date Functions
- DATEDIF Syntax:
=DATEDIF(start_date, end_date, unit) - EOMONTH Syntax:
=EOMONTH(start_date, months) - WORKDAY.INTL Syntax:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
Mastering the DATEDIF, EOMONTH, and WORKDAY.INTL functions in Google Sheets opens up a world of possibilities for date calculations, whether you’re working on financial analysis, project management, or simple day-to-day tasks. By applying these functions effectively, you can enhance your workflow, reduce manual errors, and improve the accuracy of your date-related calculations. Start using them today to take your Google Sheets skills to the next level!