Last Working Day of a Given Year – Google Sheets Formula

In this walkthrough guide, you can learn to find the last working day of a given year in Google Sheets. It’s just a simple formula.

There are two approaches, and we will follow the shorter one.

The first approach is populating a list of dates from 21st to 31st December using SEQUENCE, filtering out weekends, and returning the max date.

In the other approach, we will use the WORKDAY or WORKDAY.INTL date functions. It’s the shorter one.

How do WORKDAY and WORKDAY.INTL functions differ in evaluating the last business day of a given year?

You can exclude holidays (other than weekends) by specifying them in WORKDAY.INTL.

Determining the Weekends (String Method)

Before proceeding further, you should know how to specify the weekend(s) to exclude while evaluating the last working day of the provided year.

There are two methods (codes): Number and String.

The easiest way to remember weekend code is to follow the string method, and here is an example.

There will be seven numbers as a string representing days from Monday to Sunday, where 0 and 1 represent work day and weekend, respectively.

String Method (Sat-Sun Weekends): "0000011"

The above string represents Saturday and Sunday weekends and which is the default one in the function. So you can omit it if your weekends are the same.

How do we specify Friday and Saturday as the weekend in the WORKDAY.INTL function?

String Method (Fri-Sat Weekends): "0000110"

If you understand the above, please proceed to the formula that finds the last business day of the year.

Last Working Day of a Given Year

I want to find the last working day of 2016 specified in cell B3.

To find the last business day, use the following formula. I haven’t specified the weekend string since they are the default ones.

=workday.intl(date(B3,12,31)+1,-1)
Last Working Day of a Given Year Formula

To specify Friday-Saturday as the weekend, you can use the following formula.

=workday.intl(date(B3,12,31)+1,-1,"0000110")

How does this formula work?

With the help of the DATE function, we could generate the last date in 2016, which is 31/12/2016.

date(B3,12,31)

We have added 1 to it to get the first day of the following year.

date(B3,12,31)+1

The WORKDAY.INTL function counts backward because of the negative number to advance and returns the last working day of the given year.

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

To specify holidays, if any, enter them in a cell or cell range and use that reference as the last parameter in the formula.

Last Working Day of the Current, Last, or Next Year in Google Sheets

If you understand the above formula, it’s easy to find the last working/business day of the current, previous, or next year in Google Sheets.

For that, you should replace B3 in the formula with either of the below codes.

Current Year: Replace B3 with year(today())

Last/Previous Year: Replace B3 with year(today())-1

Next Year: Replace B3 with year(today())+1

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.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

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...

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.