CODEWITHSUNDEEP
excelvbadate
Florian
Florian

Reputation: 129

Worday VBA with EOM function not giving a non workday result

I am trying to get the last workday of a month, in relation to a set date -1 month:

Function below returns 30/04/2022 (which is a Saturday), I don't understand why as workday should be returning 31/05/2022 - 1 month, workday = 29/04/2022?

Arrholidays is an array of holidays
Report date = 31/05/2022

PnLD1WS.Cells(i, 159).Value = Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(ReportDate, -1), 0, ArrHolidays)

Also tried reversed, still gives 30/04/2022 and not 29/04/2022

PnLD1WS.Cells(i, 159).Value = Application.WorksheetFunction.EoMonth((Application.WorksheetFunction.WorkDay(ReportDate, -1, ArrHolidays)), -1)

Upvotes: 0

Views: 53

Answers (1)

Florian
Florian

Reputation: 129

Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(ReportDate, -1) + 1, -1, ArrHolidays)

Adding =WORKDAY(DATE+1,-1)

Checks if given date is working day or not and gives closest workday

Upvotes: 1

Related Questions