First Workday of the Month

[30percent]

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!

 

[MARK858]

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.

 

[Scott T]

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.

 

[30percent]

Hi,

thank you!

it works.

I wanted use the formula in VBA. I tried this

this_Month = application.WorksheetFunction.WorkDay(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)

but somehow this is not working. Wonder if someone could help.

 

[barry houdini]

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

Formula wise this version is easier

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

 

[Tetra201]

VB-wise, try this:

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

 

[MARK858]

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

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