Are you struggling to find the first and last workdays of a month in Google Sheets? Look no further! In this article, I’ll share simple formulas that will make your life easier.
Find the First and Last Days of a Month in Google Sheets
Before diving into workdays, let’s first learn how to find the first and last days of a month in Google Sheets.
Assume we have a date in cell A2, let’s say it’s February 6, 2021. To find the last day of the month based on this date, you can use the formula =EOMONTH(A2,0). This formula will return February 28, 2021.
To find the first day of the month, you can use a little trick. There’s no built-in “start of the month” function in Google Sheets, but you can achieve the same result using the EOMONTH function. Here’s how:
=EOMONTH(A2,-1)+1
This formula will return February 1, 2021, which is the first day of the month.
These formulas work if you have a specific date in a cell. But what if you want to find the first and last days of the current, previous, or next month? That’s where the TODAY function comes in handy.
Using the TODAY Function
The TODAY function is perfect for finding the first and last days of the current, previous, and next months in Google Sheets. Here’s how you can do it:
To find the last day of the current month, you can use the formula =EOMONTH(TODAY(),0). This will give you the last day of the current month.
To find the first day of the current month, you can use the formula =EOMONTH(TODAY(),-1)+1. This will return the first day of the current month.
To find the last day of the previous month, you can use the formula =EOMONTH(TODAY(),-1). This will give you the last day of the previous month.
To find the first day of the previous month, you can use the formula =EOMONTH(TODAY(),-2)+1. This will return the first day of the previous month.
To find the last day of the next month, you can use the formula =EOMONTH(TODAY(),1). This will give you the last day of the next month.
To find the first day of the next month, you can use the formula =EOMONTH(TODAY(),0)+1. This will return the first day of the next month.
Now that we have mastered finding the first and last days of a month, let’s move on to the main event – finding the first and last workdays of a month!
Find the First and Last Workdays of a Month in Google Sheets
To find the first and last workdays of a month in Google Sheets, we will combine the EOMONTH, TODAY, and WORKDAY.INTL functions.
The WORKDAY.INTL function returns the date after a specified number of workdays, excluding weekends. It takes two arguments: start_date and num_days.
To find the last workday of a month, you can use the following formula:
=WORKDAY.INTL(EOMONTH(A2,0)+1,-1)
Here, A2 represents the date from which you want to find the last workday. This formula will exclude weekends (Saturday and Sunday) and return the last workday of the month.
To find the first workday of a month, you can use the following formula:
=WORKDAY.INTL(EOMONTH(A8,-1),1)
Here, A8 represents the date from which you want to find the first workday. This formula will return the first workday of the month, excluding weekends.
And there you have it! You now know how to find the first and last workdays of a month in Google Sheets. These formulas will save you time and effort when working with dates and workdays in your spreadsheets.
If you want to learn more tips and tricks for Google Sheets, head over to Crawlan.com. Happy spreadsheet-ing!
