In this article, you will learn how to find the last working day of a given year in Google Sheets. It’s a simple and efficient formula that can save you time and effort. Let’s dive in!
Determining the Weekends (String Method)
Before we get started, it’s important to know how to specify weekends to exclude while evaluating the last working day of the provided year. There are two methods: Number and String. For simplicity, we’ll focus on the String method.
The easiest way to remember the weekend code is to follow the string method. For example, the string “0000011” represents the default weekends, which are Saturday and Sunday. If your weekends are the same, you can omit this step.
If you want to specify Friday and Saturday as the weekend, you can use the string “0000110”. This method will be used in the formula to find the last business day of the year.
Last Working Day of a Given Year
Let’s say we want to find the last working day of the year 2016. To do this, use the following formula in your Google Sheets:
=workday.intl(date(B3,12,31)+1,-1)The formula uses the WORKDAY.INTL function, which counts backward from the given date to find the last working day. In this case, we add 1 to the last day of the year to get the first day of the following year. The negative number in the formula ensures that the function counts backward.
If you want to specify Friday and Saturday as the weekend, use the following formula:
=workday.intl(date(B3,12,31)+1,-1,"0000110")This formula works by using the DATE function to generate the last date of the specified year. By adding 1 to it, we obtain the first day of the following year. The WORKDAY.INTL function then counts backward and returns the last working day.
To specify holidays, if any, simply 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
Once you understand the formula, it becomes easy to find the last working day of the current, previous, or next year in Google Sheets.
To find the last working day of the current year, replace B3 in the formula with year(today()). For example:
=workday.intl(date(year(today()),12,31)+1,-1)To find the last working day of the previous year, replace B3 with year(today())-1.
To find the last working day of the next year, replace B3 with year(today())+1.
Conclusion
Now that you know how to find the last working day of a given year in Google Sheets, you can save time and streamline your workflow. The formulas provided are simple yet powerful, allowing you to customize them to fit your specific requirements.
If you want to learn more about Google Sheets and boost your productivity, be sure to check out Crawlan.com. They offer a wealth of resources and guides to help you excel in your spreadsheet endeavors. Happy sheeting!
