How to find first / last day or working day of a month in Excel?
Do you know how to find the first or last day and the first or last working day of a given month date in a worksheet? For example, I have a date 2014/5/21, and now I want to know the first and last day of this month, that is 2014/5/1 and 2014/5/31, to get the first business day is 2014/5/1 and the last business day is 2014/5/30. This article will describe how to extract the first or last day and first or last working day in Excel of a given month.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To return the first day of a month, you can use this Date function, please do as these:
1. Enter this formula =DATE(YEAR(B1),MONTH(B1),1) into a blank cell (B1 contains the date that you want to use), see screenshot:
2. And press Enter key, you will get the first day of this month.
Note: In order to get the last day of this month, you need to apply this formula: =DATE(YEAR(B1),1+MONTH(B1),1)-1, see screenshots:
In certain situation, you may want to know which day is the first or last business day of a given month date, and the following formulas can help you quickly calculate the results as you need.
1. To return the first working day of a month:
Apply this formula: =WORKDAY(DATE(YEAR(B1),MONTH(B1),1)-1,1) into a blank cell (B1 has the date that you want to use), and then press Enter key, the date of first working day will be calculated. See screenshots:
2.To return the last working day of a month:
Please enter this formula: =WORKDAY(DATE(YEAR(B1),MONTH(B1)+1,1),-1) to extract the last working days of a given month date, see screenshots: