Forum Discussion

Tony2021

Iron Contributor

Jun 27, 2022

Solved

Count only Weekdays

Experts, how can I adjust the [EndNxtMo] below to return the last business day of the next month? for example, it is returning Sunday July 31 but I need it to return Friday July 29. EndNxtMo: ...

access

arnel_gp
Jun 28, 2022
Business day can be Mon-Fri (does not include Sat/Sun)?
just create another function to "step back" when the date is either sat/sun:

Public Function fncLastBusinessDay(Byval FundingDate As Date) As Date
Dim dte As Date
dte = DateSerial(Year([FundingDate]),Month([FundingDate])+2,0)
Do Until Instr(1, "Sat/Sun", Format$(dte, "ddd")) = 0
dte = DateAdd("d",-1, dte)
Loop
fncLastBusinessDay = dte
End Function

so you modify your Query Expression:

EndNxtMo: fncLastBusinessDay([FundingDate])

GustavBrock

MVP

Jul 24, 2022

Tony2021

For a universal solution, that will not fail in a non-English environment, see my function DateWorkdayMonthLast in module DateWork.bas at VBA.Date

Tony2021
Iron Contributor
Jul 25, 2022
HI Gustav, good to hear from you. I can see you have a fine solution. You are the date specialist! thank you sir....