The idea behind the formulas for first working day of the month is very similar to finding the last working day.

Essentially you find the last day of the month, then you add one to it or subtract from it.

Below we will see how the combination of WORKDAY() function and EOMONTH() functions are used.

First and Last Working Day of the Month

First Working Day of the Month

Given a random date in column A, the formula

=WORKDAY(EOMONTH(A2,-1),1)

returns the first working day of the month for whatever date you put in cell A2.

The formula says find the last day of cell A2’s previous month, then add one working day to it.

The second argument, -1, in EOMONTH() function returns the end of the month of last month. If you put 0 instead, it will return the end of month of the date in cell A2.

WORKDAY() function calculates the working day that is x days from a certain date.

WORKDAY() function has three arguments.

Start_date: The start date from which resulting work day is calculated

Days: Number of days, x, from start date.

Holiday: (Optional). You can specify a range of dates using cell reference or array values to specify the dates to exclude.

Last Working Day of the Month

Given a random date in column A, the formula

=WORKDAY(EOMONTH(A2,0)+1,-1)

returns the last working day for whatever date you put in cell A2.

The formula says find the end of month of cell A2’s current month, then add one day to make sure it returns the first date next month. You then use WORKDAY() function to find the previous working day.

Conclusion

EOMONTH() is a very useful function in Excel date calculations. Other functions are DATEDIF() functions and EDATE() functions. See Related Posts below to learn about DATEDIF().


Related Posts

Excel Number of Days Between Dates

Convert Date to Month in Excel

Pin It on Pinterest

Share This