First Workday of the Month

Thanks:  0
Likes:  0

# Thread: First Workday of the Month

1. ## First Workday of the Month

Hi,

I have seen many articles or posts on the internet that using the following formula, I could get the first workday of the month.

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)

However, the result of the formula is 43040. It doesn't make sense to me. Do I need to convert it first before I get say a day between 1st to 3rd?

Thank you!

2. ## Re: First Workday of the Month

You need to format the cell as a date. Right click, format cells - select one of the date formats and 43040 will give you November 1st.

3. ## Re: First Workday of the Month

Excel dates are just numbers formatted to be dates. Format the cell as a date and you will see that 43040 is 11/1/17.

4. ## Re: First Workday of the Month

Hi,

thank you!

it works.

I wanted use the formula in VBA. I tried this
Code:
this_Month = application.WorksheetFunction.WorkDay(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
but somehow this is not working. Wonder if someone could help.

5. ## Re: First Workday of the Month

Originally Posted by 30percent
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
Formula wise this version is easier

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

6. ## Re: First Workday of the Month

VB-wise, try this:

Code:
Dim this_Month As Date
this_Month = WorksheetFunction.WorkDay(Date - Day(Date), 1)

7. ## Re: First Workday of the Month

Adding to what Barry stated for the formula, in VBA try....

Code:
Sub xxxx()
Dim this_Month As Date
this_Month = WorksheetFunction.WorkDay(WorksheetFunction.EoMonth(Date, -1), 1)
Range("a1") = this_Month
End Sub

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•