Forum Discussion
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: DateSerial(Year([FundingDate]),Month([FundingDate])+2,0)
FYI: I am using it in the following:
DayCountIIF: IIf([EndThisMo]-[FundingDate]<6,[EndNxtMo]-[FundingDate],[EndThisMo]-[FundingDate])
thank you
- 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])
4 Replies
HI Gustav, good to hear from you. I can see you have a fine solution. You are the date specialist! thank you sir....
- 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])- Amazing! thank you very much Arnel.
