Finding the First and Last Workdays of a Month in Google Sheets

Published on

This post provides simple formulas to help you find the first and last workdays of any given month in Google Sheets.

You can determine these for the current month, the previous month, or any specified month.

To achieve this, we utilize the WORKDAY.INTL function in conjunction with EOMONTH function. In some cases, the TODAY and EDATE functions are also necessary.

But when do we need to use the TODAY and EDATE functions?

To identify the first and last workdays of the current, previous, or upcoming month, you will need to incorporate the TODAY function, the EDATE function, or both into the process.

Finding the Last Workday of the Current Month

To find the last workday of the current month in Google Sheets, enter this formula into any cell:

=WORKDAY.INTL(EOMONTH(TODAY(), 0) + 1, -1, "0000011")

The EOMONTH function returns the end-of-month date for the start_date, which is TODAY().

Syntax: EOMONTH(start_date, months)

Setting months to 0 and start_date to TODAY() ensures it returns the last day of the current month. Adding 1 to this result gives the first day of the next month.

The WORKDAY.INTL function uses this date as the start_date and moves num_days backwards by 1 working day (-1).

Syntax: WORKDAY.INTL(start_date, num_days, [weekend], [holidays])

The weekend parameter uses a sequence of seven digits (Monday through Sunday), where each digit represents whether the day is considered a workday (0) or a weekend/holiday (1).

In this context, “0000011” signifies that Saturday and Sunday are considered weekends. Adjust this sequence according to your specific weekend configuration.

Finding the First Workday of the Current Month

To find the first workday of the current month, use this formula:

=WORKDAY.INTL(EOMONTH(TODAY(), -1), 1, "0000011")

The EOMONTH function returns the last day of the previous month (TODAY() with months as -1). WORKDAY.INTL then advances 1 working day from this date, giving you the first workday of the current month.

Using a Specific Date Instead of TODAY

With minimal changes to the formulas above, you can determine the first and last workdays for a specific date.

Replace TODAY() in the formulas with the specific date in the format DATE(year, month, day) or reference the date from a specific cell.

For example, to find the last workday of December 2024, use this formula:

=WORKDAY.INTL(EOMONTH(DATE(2024, 12, 1), 0)+1, -1, "0000011")

To find the first workday of January 2025, use this formula:

=WORKDAY.INTL(EOMONTH(DATE(2025, 1, 1), -1), 1, "0000011")

Finding the First and Last Workdays of the Previous or Next Month

In this case, we will use the EDATE function with the TODAY function. This combination returns a date that is a specified number of months before or after the current date.

In our earlier formulas, we need to replace TODAY with EDATE as follows:

This formula returns the last workday of the previous month:

=WORKDAY.INTL(EOMONTH(EDATE(TODAY(), -1), 0)+1, -1, "0000011")

And this one returns the first workday of the previous month:

=WORKDAY.INTL(EOMONTH(EDATE(TODAY(), -1), -1), 1, "0000011")

To find the last workday of the next month, use this formula:

=WORKDAY.INTL(EOMONTH(EDATE(TODAY(), 1), 0)+1, -1, "0000011")

This formula returns the first workday of the next month:

=WORKDAY.INTL(EOMONTH(EDATE(TODAY(), 1), -1), 1, "0000011")

Resources

Here are some additional resources:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.