VBA for a date in a holiday list not working right

[Lightkeepr]

I have the following VBA code that the first part where it looks to see if the date is a weekend date works great. It adjusts the dates backwards to the next available Mon-Fri date. The second step is for it to check the date against a list of dates that are holidays. This step doesn't seem to be doing anything and I cannot figure out why. I have it looping three time so that it checks to make sure that the new adjusted date (if one was found on the list) is not also a holiday on the list. Any ideas of where I'm missing something? In the worksheet that I am using this, column A has the ideal date for a delivery, in column D I have the formula =DateChecker(A5) to give me the adjusted date based on weekends/holidays off.

Function DateChecker(ByVal dateIO As Date) As Date
    Dim SearchRng As Range
    Dim DateSearch As String
    Dim resMatch As Variant
    Dim LoopCheck As Integer
'    Application.Volatile True

    Set SearchRng = ThisWorkbook.Worksheets("Holidays").Range("C5:C55")

'Check if date is weekend and adjust it
    If Weekday(dateIO, 2) = 7 Then
        dateIO = dateIO - 2
    ElseIf Weekday(dateIO, 2) = 6 Then
        dateIO = dateIO - 1
    End If

    LoopCheck = 3
    
'check date against the holidays list
    For LoopCheck = 1 To LoopCheck
        DateSearch = dateIO
        resMatch = Application.Match(DateSearch, SearchRng, 0)
        
        If IsError(resMatch) Then
            GoTo Last
        Else
            dateIO = dateIO - 1
        End If
    Next LoopCheck

Last:
'Check if final date is weekend and adjust it
    If Weekday(dateIO, 2) = 7 Then
        dateIO = dateIO - 2
    ElseIf Weekday(dateIO, 2) = 6 Then
        dateIO = dateIO - 1
    End If

    DateChecker = dateIO

End Function

 

[Joe4]

Is there some reason why you cannot just use the built-in Workday function in Excel (no reason to recreate something that already exists)?
See: How to use the Excel WORKDAY function | Exceljet

 

[Lightkeepr]

Is there some reason why you cannot just use the built-in Workday function in Excel (no reason to recreate something that already exists)?
See: How to use the Excel WORKDAY function | Exceljet

When I used the WORKDAY function it would roll the day to the next forward available date and I need it to go to the previous available date.

 

[Joe4]

Can you show us a small sample of your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Lightkeepr]

Can you show us a small sample of your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here you go. The dates in pink cells are listed as holidays in the named range, gray is a weekend date. I have also provided the results of using the function in column D, the WORKDAY formula in E and what the desired result is in column F if that helps.

Site Kit Delivery Schedule-NEW.xlsm
	B	C	D	E	F
1			DateChecker	Workday	Desired Result
2	Date Needed By		Delivery Date	Delivery Date	Delivery Date
3	Thu, 07/14/2022		Thu, 07/14/2022	Thu, 07/14/2022	Thu, 07/14/2022
4	Fri, 07/15/2022		Fri, 07/15/2022	Fri, 07/15/2022	Fri, 07/15/2022
5	Sat, 07/16/2022		Fri, 07/15/2022	Mon, 07/18/2022	Fri, 07/15/2022
6	Sun, 07/17/2022		Fri, 07/15/2022	Mon, 07/18/2022	Fri, 07/15/2022
7	Mon, 07/18/2022		Mon, 07/18/2022	Mon, 07/18/2022	Mon, 07/18/2022
8	Tue, 07/19/2022		Tue, 07/19/2022	Tue, 07/19/2022	Tue, 07/19/2022
9	Wed, 07/20/2022		Wed, 07/20/2022	Fri, 07/22/2022	Tue, 07/19/2022
10	Thu, 07/21/2022		Thu, 07/21/2022	Fri, 07/22/2022	Tue, 07/19/2022
11	Fri, 07/22/2022		Fri, 07/22/2022	Fri, 07/22/2022	Fri, 07/22/2022
12	Sat, 07/23/2022		Fri, 07/22/2022	Mon, 07/25/2022	Fri, 07/22/2022
13	Sun, 07/24/2022		Fri, 07/22/2022	Mon, 07/25/2022	Fri, 07/22/2022
14	Mon, 07/25/2022		Mon, 07/25/2022	Mon, 07/25/2022	Mon, 07/25/2022
15	Tue, 07/26/2022		Tue, 07/26/2022	Tue, 07/26/2022	Tue, 07/26/2022
16	Wed, 07/27/2022		Wed, 07/27/2022	Wed, 07/27/2022	Wed, 07/27/2022
17	Thu, 07/28/2022		Thu, 07/28/2022	Thu, 07/28/2022	Thu, 07/28/2022
18	Fri, 07/29/2022		Fri, 07/29/2022	Fri, 07/29/2022	Fri, 07/29/2022
19	Sat, 07/30/2022		Fri, 07/29/2022	Mon, 08/01/2022	Fri, 07/29/2022
20	Sun, 07/31/2022		Fri, 07/29/2022	Mon, 08/01/2022	Fri, 07/29/2022
21	Mon, 08/01/2022		Mon, 08/01/2022	Mon, 08/01/2022	Mon, 08/01/2022
22	Tue, 08/02/2022		Tue, 08/02/2022	Tue, 08/02/2022	Tue, 08/02/2022
23	Wed, 08/03/2022		Wed, 08/03/2022	Wed, 08/03/2022	Wed, 08/03/2022
Dates

Cell Formulas
Range	Formula
D3:D23	D3	=DateChecker(B3)
E3:E23	E3	=WORKDAY(B3-1,1,holidays)

Named Ranges
Name	Refers To	Cells
holidays	=Holidays!$C$4:$C$39	E3:E23

 

[Joe4]

See if this formula gets you what you need (with no VBA required):

=IF(WORKDAY(B3-1,1,holidays)=B3,B3,WORKDAY(B3-1,0,holidays))

 

[Lightkeepr]

See if this formula gets you what you need (with no VBA required):

=IF(WORKDAY(B3-1,1,holidays)=B3,B3,WORKDAY(B3-1,0,holidays))

That works so long as there are not two holiday dates back to back as in this example. It corrects the 07/20/2022 Date Needed By to 7/19/2022 as expected but the 7/21/2022 Date Needed By calculates to 7/20/2022 which is a holiday.

This is why I was attempting to do this in VBA as I figured I could have the VBA adjust the date, check the adjusted against the holidays and if the adjusted date is on the list it would adjust it again until it's not on a holiday date or a weekend date.

 

[Joe4]

Can you post your holiday list, so I can set it up exactly the same way on my side, to ensure that I am testing the same data you are (I am not sure where you are, but those aren't holidays where I am located)?

 

[Lightkeepr]

Can you post your holiday list, so I can set it up exactly the same way on my side, to ensure that I am testing the same data you are (I am not sure where you are, but those aren't holidays where I am located)?

Those dates are not recognized holidays, they are to simulate days the work site will be closed.

Site Kit Delivery Schedule-NEW.xlsm
	C	D
3	Date	Holiday
4	07/20/2022	Site Closed
5	07/21/2022	Site Closed
6	09/05/2022	Labor Day
7	10/10/2022	Columbus Day
8	11/11/2022	Veterans Day
9	11/24/2022	Thanksgiving
10	11/25/2022	Thanksgiving
11	12/25/2022	Christmas Day
12	12/26/2022	Christmas Day Recognized
13	12/31/2022	New Years Eve
14	01/01/2023	New Years Day
15	01/16/2023	MLK Day
16	05/29/2023	Memorial Day
17	06/19/2023	Juneteenth
18	07/04/2023	Independence Day
19	09/04/2023	Labor Day
20	10/09/2023	Columbus Day
21	11/10/2023	Veterans Day
22	11/23/2023	Thanksgiving
23	11/24/2023	Thanksgiving
24	12/25/2023	Christmas Day
25	12/31/2023	New Years Eve
26	01/01/2024	New Years Day
27
28
29
30
31
32
33
34
35
36
37
38
39
Holidays

 

[Joe4]

I see the conundrum now. I think you can extend the formula to check another level to get it to work, but then what happens if you have 3 in a row, etc.
So here is a simple User Defined Function that should do what you want:

Function MyDateChecker(dtInput As Date) As Date

    Dim dt1 As Date
    Dim rngHol As Range
    
'   Set initial values
    dt1 = dtInput
    Set rngHol = ThisWorkbook.Worksheets("Holidays").Range("C5:C55")
    
'   Loop through days backwards until you find a valid weekday that is not a holiday
    Do
        If (Weekday(dt1, 2) < 6) And Application.WorksheetFunction.CountIf(rngHol, dt1) = 0 Then
            MyDateChecker = dt1
            Exit Do
        Else
            dt1 = dt1 - 1
        End If
    Loop

End Function

Basically, we just loop and keep subtracting one from the date until we get to a valid weekday that is not a holiday.