Exclude last business day of the month

[swamy567]

Hi !
i need to do quarter rate delete exclude last businss day of the month.
do you have any function for that please if you have please provide, thanks in advance.

 

[SkipVought]

Hi,

DateSerial(yr, mo+1, 0) will return the last day of mo.

Workday(YourDate) returns the day of week for YourDate.

with these two, you can calculate the last workday of the month, without regard to holidays.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

this is the function i am doing here. but some how it is not picking up the friday's
could you please check. thanks i advance
Public Function LastDay(intOriginYear As Integer, intOriginMonth As Integer, ByVal strEffDate As Date, ByVal InputRateRead, ByVal RevalPriorToDate) As Variant
' **************************************************************************
' * Checks to see if the date from the file is the last day of the month. *
' * Prints if the day is NOT the last day of the month. *
' ***************************************************************************


Dim intCheck, intDayName, strDiff
Dim strDate, strCheckDate As Date

LastDay = Day(DateSerial(intOriginYear, intOriginMonth + 1, 0))
strDate = Day(strEffDate)

strCheckDate = intOriginMonth & "/" & LastDay & "/0" & intOriginYear
strDiff = DateDiff("d", strEffDate, strCheckDate, vbUseSystemDayOfWeek)
intDayName = Weekday(Format(strEffDate, "mmm dd, yyyy"))
strCurrentDay = Format(strEffDate, "dddd")
strNextDay = Format(strCheckDate - 1, "dddd")
strAfterNextday = Format(strCheckDate, "dddd")
strGeneralDate = Mid(strEffDate, 1, 5)

If strDate = LastDay Then
Else

If strDiff <= 3 And strNextDay = "Saturday" Or strNextDay = "Saturday" Then
'If strDiff <= 2 And strNextDay = "Saturday" Or strNextDay = "Saturday" Then
If intDayName = 1 Or intDayName = 7 Then 'Sun or Sat
Else
strNumEffDate = Format(strEffDate, "General Number")
strNow = Format(Now, "mm/dd/yy")
strNowMonth = Format(Now, "mm")
strNowConstantDay = "01"
strNowYear = Format(Now, "yyyy")
strNumber = strNowMonth & "/" & strNowConstantDay & "/" & strNowYear
strStaticRevalPriorToDate = Format(RevalPriorToDate, "General Number") '

If strCurrentDay = "Friday" And strNextDay = "Saturday" And strAfterNextday = "Sunday" Then
ElseIf strCurrentDay = "Friday" And strNextDay = "Friday" And strAfterNextday = "Saturday" Then
Else
If strNumEffDate < strStaticRevalPriorToDate Then
Print #2, InputRateRead
End If
End If
End If
Else
If intDayName = 1 Or intDayName = 7 Then 'Sun or Sat
Else
strNumEffDate = Format(strEffDate, "General Number")
strNow = Format(Now, "mm/dd/yy")
strNowMonth = Format(Now, "mm")
strNowConstantDay = "01"
strNowYear = Format(Now, "yyyy")
strFullDate = strNowMonth & "/" & strNowConstantDay & "/" & strNowYear
strStaticRevalPriorToDate = Format(RevalPriorToDate, "General Number") '

If strCurrentDay = "Friday" And strNextDay = "Saturday" And strAfterNextday = "Sunday" Then
'ElseIf strCurrentDay = "Friday" And strAfterNextday = "Saturday" Then
ElseIf strCurrentDay = "Friday" And strNextDay = "Friday" And strAfterNextday = "Saturday" Then
Else
If strNumEffDate < strStaticRevalPriorToDate Then
Print #2, InputRateRead
End If
End If
End If
End If

End If

End Function

 

[SkipVought]

I was thinging about something like this...

Public Function LastWorkday(dDate As Date) As Variant
    Dim dLast As Date, iDay As Integer
    dDate = DateSerial(Year(dDate), Month(dDate) + 1, 0)
    LastWorkday = dDate
    Do
        iDay = Application.Weekday(LastWorkday)
        Select Case iDay
            Case 2 To 6
                Exit Function
        End Select
        LastWorkday = LastWorkday - 1
    Loop
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

Skip
Thanks fr your help. still it is showing .
could yuo help me
thanks in advance

 

[SkipVought]

still it is showing .

What?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

skip
first thank yu very much for your great help
the my (above)function iam trying. it coms may june, july last business day of the month wiht rate
but somehow it also shows may 06, may 2m nay 9, may 16m may 23, and also may 30th
somehow if clause is wrongly misplaced. could you please check
thanks

 

[SkipVought]

Your function is way way too convoluted. Just use my simple function...

YourDate = DateValue("5/6/2008")
if YourDate = LastWorkday(YourDate) then
   msgbox "Last workday of month"
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

Skip
thnnks. ctualy every quarter remove rate from Mainframe through Attachmat macrt. if i run macro today for exampl septmber it deletes rates from March, pril May except last business of mnth.
i wrote thorugh vb 6 and write the macro in attachmate. i think it is easier.
but this function only have problem whihc is only may month. other months ok the same thing if i remove dec, january feb. Janaury month is not deleteng all . it showsin frieday's date. anyway i willtry this one.and let you know.
thanks