CODEWITHSUNDEEP
vbaexcel
S.BaB
S.BaB

Reputation: 21

Add Workday in if function

I have an if condition that runs a function if time is between 5:10 pm and 8:15 pm.

How can I add another condition that says run the function only if it is a work day and if time is between 5:10pm and 8:15pm.

Please see code below:

If Format(Time, "hhmm") > 1710 And Format(Time, "hhmm") < 2015 Then
        Do stuff
    Else
        Stop
    End If

I would like to avoid running the code during the weekend and bank holiday.

many Thanks

Upvotes: 2

Views: 509

Answers (3)

michwalk
michwalk

Reputation: 313

I think the workday function would work for your situation. You can either store the dates in a range in Excel or in an array in the code. I did a couple of the holidays.

Sub isTimeToDoThings()

    Dim time As Date
    Dim tomorrow As Date
    Dim nextWorkDay As Date
    Dim holidays(3) As Date
    Set wf = Application.WorksheetFunction

    holidays(0) = CDate("1 / 1 /" & Year(Now())) 'New Years
    holidays(1) = CDate(ThanksgivingDate(Year(Now()))) 'Thanksgiving

    time = Date
    tomorrw = Date + 1
    nextWorkDay = CDate(wf.WorkDay(time, 1))

    If Format(time, "hhmm") > 1710 And Format(time, "hhmm") < 2015 _
    And tomorrow = nextWorkDay Then
        'Do stuff
    Else
        Stop
    End If


End Sub

Public Function ThanksgivingDate(Yr As Integer) As Date
   ThanksgivingDate = DateSerial(Yr, 11, 29 - _
       Weekday(DateSerial(Yr, 11, 1), vbFriday))
End Function

Upvotes: 1

user4039065
user4039065

Reputation:

Tackle your times as actual time values, not strings that look like a time value with the TimeSerial function. Use the Weekday function to determine the numerical values on Mon-Fri.

If Time >= TimeSerial(17, 10, 0) And Time <= TimeSerial(20, 15, 0) And Weekday(Date, 2) <6 Then
    Do stuff
Else
    Stop
End If

Upvotes: 2

Weasemunk
Weasemunk

Reputation: 455

Use the IsWeekend function in @Zaider's link. Like @Forward Ed mentioned, you will need a list of bank holidays. Once you have those, you should store them on some sheet in an ascending list. Then do:

Dim holiday As Boolean
For holRow = 1 To N
    If Month(Date) = Month(Cells(holRow,1).Value) And _ 
                     Day(Date) = Day(Cells(holRow,1).Value) Then
        holiday = True
    End If
Next
If Not holiday And Not IsWeekend(Date) And _ 
   Format(Time, "hhmm") > 1710 And Format(Time, "hhmm") < 2015 Then
    Do Stuff
Else
    Stop
End If

EDIT: Forgot VBA doesn't have AndAlso

Upvotes: 1

Related Questions