VBA code to calculate working days from given dates in a unique way

[kelly mort]

I have a situation here with me and I need someone to help me to fix it.

There are two textboxes which contain dates
That is textbox1 and textbox2

Then I have two columns with dates as well.
Thats columns AO and AP.
AO3 and AP3 are headers with the labels START and ENDED respectively.

Now what I want to do is like this:
If say the dates from AO4 and AP4 fall within the dates from the textboxes, then I want to find the number of working days that are there in the two dates (AO4 and AP4).

I can't seem to figure it out ATM. I will be very glad if someone could help out with it.

Thanks in advance.

Kelly Mort

 

[BenMcBen]

JUst a brief hint - you've looked at the function NETWORKDAYS()? Or is the problem more about finding the dates to input?

 

[kelly mort]

JUst a brief hint - you've looked at the function NETWORKDAYS()? Or is the problem more about finding the dates to input?

Yes I have looked at that function
I even have other versions that I use for similar functions.

Here, the dates in the two defined columns above could be more, which means that i would need to do some loops and so on.

That is what i can't figure out


Also, i am storing the dates in the format dd-mm-yy
Cells formatted as text

 

[kelly mort]

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer
    
    On Error GoTo Err_Work_Days
    
    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0
    
    While DateCnt <= EndDate
       If Format(DateCnt, "ddd") <> "Sun" And _
           Format(DateCnt, "ddd") <> "Sat" Then
           EndDays = EndDays + 1
       End If
       DateCnt = DateAdd("d", 1, DateCnt)
    Wend
    
    Work_Days = WholeWeeks * 5 + EndDays
    
   Exit Function
    
Err_Work_Days:
 
    If Err.Number = 94 Then
       Work_Days = 0
       Exit Function
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If
End Function
 
 
 
Sub TEST_DAYS()
   Dim sDate As String
   Dim eDate As String
   Dim SH As Object
   Dim lr As Long
   Dim d As Range
   Dim DAT_TAB As Range
   Dim wDays#
   
   Set SH = Sheets("LOG")
   
   With SH
       sDate = .Range("AO2").Text
       eDate = .Range("AP2").Text
       lr = .Cells(Rows.Count, "AO").End(xlUp).Row
       If lr < 4 Then lr = 4
       Set DAT_TAB = .Range("AO4:AO" & lr)
   End With
   
   wDays = 0
   For Each d In DAT_TAB
       If Len(d) And Len(d.Offset(, 1)) Then
           If CDate(sDate) <= CDate(d) And CDate(eDate) >= CDate(d.Offset(, 1)) Then
               wDays = wDays + Work_Days(CDate(d), CDate(d.Offset(, 1)))
           End If
       End If
   Next d
 
   MsgBox wDays
End Sub

I have been able to figure it out at last