Seamus2626
asked on
Amend date function
Hi,
Below is a date function that i use to test to see whether it is the first working day of the month and if it is i do some other stuff.
Can i have this amended so it checks to see if it is the 2nd day of the working day of the month
Thanks
Seamus
Function IsFirstWorkingDay(dt As Date) As Boolean
Dim FirstWorkingDay As Date
FirstWorkingDay = DateSerial(Year(dt), Month(dt), 1)
Select Case Weekday(FirstWorkingDay, vbMonday)
Case 6
FirstWorkingDay = FirstWorkingDay + 2
Case 7
FirstWorkingDay = FirstWorkingDay + 1
End Select
IsFirstWorkingDay = dt = FirstWorkingDay
End Function
Below is a date function that i use to test to see whether it is the first working day of the month and if it is i do some other stuff.
Can i have this amended so it checks to see if it is the 2nd day of the working day of the month
Thanks
Seamus
Function IsFirstWorkingDay(dt As Date) As Boolean
Dim FirstWorkingDay As Date
FirstWorkingDay = DateSerial(Year(dt), Month(dt), 1)
Select Case Weekday(FirstWorkingDay, vbMonday)
Case 6
FirstWorkingDay = FirstWorkingDay + 2
Case 7
FirstWorkingDay = FirstWorkingDay + 1
End Select
IsFirstWorkingDay = dt = FirstWorkingDay
End Function
Try
Function IsSecondWorkingDay(dt As Date) As Boolean
Dim SecondWorkingDay As Date
SecondWorkingDay = DateSerial(Year(dt), Month(dt), 2)
Select Case Weekday(SecondWorkingDay , vbMonday)
Case 6
FirstWorkSecondWorkingDay = SecondWorkingDay + 2
Case 7
SecondWorkingDay = SecondWorkingDay + 1
End Select
IsSecondWorkingDay = dt = SecondWorkingDay
End Function
ASKER
I have tried to use these but my syntax in the sub is wrong, i am trying
But am getting argument not optional and its pointing to IsSecondWorkingDay
How can i call this function, ask if its true and if it is, do the sub DoMonthEnd
Thanks
Seamus
If IsSecondWorkingDay = True Then
DoMonthEnd
End If
End If
But am getting argument not optional and its pointing to IsSecondWorkingDay
How can i call this function, ask if its true and if it is, do the sub DoMonthEnd
Thanks
Seamus
If IsSecondWorkingDay = True Then
DoMonthEnd
End If
End If
You have to pass it a date to check, just as you did the original function. For example, to check today:
If IsSecondWorkingDay(Date) Then
ASKER
Hey guys, both of your functions bring me into DoMonthEnd today, when i shouldnt be coming in today as it is not the 2nd working day of the month. It is the first working day.
SecondWorkingDay
This equals 02/04/2012 in your code Rory when it should be 03/04/2012
Thanks
Seamus
SecondWorkingDay
This equals 02/04/2012 in your code Rory when it should be 03/04/2012
Thanks
Seamus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats the one, thanks!
Function IsFirstWorkingDay(dt As Date) As Boolean
Dim FirstWorkingDay As Date
FirstWorkingDay = DateSerial(Year(dt), Month(dt), 1)
Select Case Weekday(FirstWorkingDay, vbMonday)
Case 6
FirstWorkingDay = FirstWorkingDay + 3
Case 7
FirstWorkingDay = FirstWorkingDay + 2
End Select
IsFirstWorkingDay = dt = FirstWorkingDay
End Function