First working day of the previous month

[a_mahey]

Hello all,

Can someone help in defining a VBA variable which will return a date which will be the first working date of the last month. So for example, today's date is 01/06/2015 but I want the variable to return based on today's date the first working day of last month which is 01/05/2015 which was a Friday.

many thanks in advance

 

[Momentman]

Excel 2013
	A	B
1	01-Jun-15	01-May-15
2	01-Jul-15	01-Jun-15
3	01-Dec-15	02-Nov-15
Sheet5

Cell Formulas
Range	Formula
B1	=WORKDAY(EOMONTH(A1,-2),1)

 

[a_mahey]

thanks a lot - could you please help to define it in VBA.

Regards

 

[Momentman]

thanks a lot - could you please help to define it in VBA.

Regards

Maybe

Excel 2013
	A	B
1	01-Jun-15	01/05/2015
2	01-Jul-15	01/06/2015
3	01-Dec-15	02/11/2015
Sheet7

Cell Formulas
Range	Formula
B1	=FirstWrkDay(A1)

and the UDF is

]
Function FirstWrkDay(sedate As Date)
    Application.Volatile
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    FirstWrkDay = Format(wf.WorkDay(wf.EoMonth(sedate, -2), 1), "dd/mm/yyyy")
End Function