Forum Discussion

Tony2021's avatar
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's avatar
    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's avatar
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's avatar
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....

Resources