Get enddatetime if X no. of working hours is added to startdatetime

Hi I am facing difficult in getting the correct enddate time.
Please refer to my attachment. Those in Red are populating the wrong enddatetime. Can help to check my vba function?


This are the criteria:

1: Working hours for Weekday 8.30 am to 5.30 pm excluding lunch break from 12 pm to 1 pm
2: Working hours for Weekends / PH is from 8.30 am to 12.30 pm (no lunch)

My intention is to get the enddatetime if X no. of working hours is added to the startdatetime.

A couple of observations, without changing your code too drastically.

Function AddWorkingHours(startDate As Date, hoursToAdd As Integer) As Date
    
    Dim currentDateTime As Date
    Dim hoursRemaining As Integer
    Dim lunchStart As Date
    Dim lunchEnd As Date
    Dim publicHolidays As Collection
    Dim holiday As Variant
    
    ' Flag for current date is a public holiday
    Dim isHoliday As Boolean
    isHoliday = False
        
    ' Define public holidays
    Set publicHolidays = New Collection
    publicHolidays.Add DateSerial(2024, 1, 1)    ' 01-Jan-2024
    publicHolidays.Add DateSerial(2024, 2, 11)   ' 11-Feb-2024
    publicHolidays.Add DateSerial(2024, 2, 12)   ' 12-Feb-2024
    publicHolidays.Add DateSerial(2024, 3, 29)   ' 29-Mar-2024
    publicHolidays.Add DateSerial(2024, 4, 10)   ' 10-Apr-2024
    publicHolidays.Add DateSerial(2024, 5, 1)    ' 01-May-2024
    publicHolidays.Add DateSerial(2024, 5, 22)   ' 22-May-2024
    publicHolidays.Add DateSerial(2024, 6, 17)   ' 17-Jun-2024
    publicHolidays.Add DateSerial(2024, 8, 9)    ' 09-Aug-2024
    publicHolidays.Add DateSerial(2024, 10, 31)  ' 31-Oct-2024
    publicHolidays.Add DateSerial(2024, 12, 25)  ' 25-Dec-2024
        
    ' loop the collection for a match - if one is found then the
    ' date passed is a holiday
    For Each holiday In publicHolidays
        '  currentDateTime was used here (and not startDate) but it is not given a value
        ' until further down in the code - meaning no date would be marked as a holiday
        If DateValue(startDate) = holiday Then
            isHoliday = True
            Exit For
        End If
    Next holiday

    ' cleaning up the code a little
    ' if the date passed is a holiday then
    If isHoliday Then
        
        ' if the day number is greater than 5 (meaning Fri, Sat or Sun)
        If Weekday(startDate, vbMonday) > 5 Then
            If TimeValue(startDate) > TimeValue("12:30:00") Then
               startDate = DateValue(startDate) + TimeValue("12:30:00")
            End If
            
            If TimeValue(startDate) < TimeValue("8:30:00") Then
             startDate = DateValue(startDate) + TimeValue("8:30:00")
            End If
        End If
        
    Else
    
        ' the date passed is not a holiday and the day is a week day
        If Weekday(startDate, vbMonday) < 6 Then
        
            ' if the time passed with start date is less than 8:30am then add 8.5 hours to start date
            If TimeValue(startDate) < TimeValue("8:30:00") Then
                startDate = DateValue(startDate) + TimeValue("8:30:00")
            End If
        
            If TimeValue(startDate) < TimeValue("13:00:00") And TimeValue(startDate) > TimeValue("11:59:59") Then
                startDate = DateValue(startDate) + TimeValue("13:00:00")
            End If
        
            If TimeValue(startDate) > TimeValue("17:30:00") Then
                startDate = DateValue(startDate) + TimeValue("17:30:00")
            End If
        
        End If
    
    End If

    currentDateTime = startDate
    hoursRemaining = hoursToAdd
 
    Do While hoursRemaining > 0
        ' Define lunch break times
        lunchStart = TimeSerial(12, 0, 0)
        lunchEnd = TimeSerial(13, 0, 0)
        
        ' Add one hour to the current datetime  - WHY???
        currentDateTime = currentDateTime + TimeValue("01:00:00")
        
        ' using Hour function returns an integer so comparing it to 8.5 and 12.5
        ' will not work if you you're trying to check for say 12 hours and 30 minutes
        
        If isHoliday Then
            If Hour(currentDateTime) >= 8.5 And Hour(currentDateTime) < 12.5 Then
                ' Decrease the remaining hours to add
                hoursRemaining = hoursRemaining - 1
            End If
             
        ElseIf Weekday(currentDateTime, vbMonday) < 6 And Not isHoliday Then     ' Regular weekday
            If (Hour(currentDateTime) >= 8.5 And Hour(currentDateTime) < 12) Or (Hour(currentDateTime) >= 13 And Hour(currentDateTime) < 17.5) Then
                ' Exclude lunch break
                hoursRemaining = hoursRemaining - 1
                
            End If
            
        ElseIf Weekday(currentDateTime, vbMonday) > 5 And Not isHoliday Then
            If Hour(currentDateTime) >= 8.5 And Hour(currentDateTime) < 12.5 Then
               ' Decrease the remaining hours to add
               hoursRemaining = hoursRemaining - 1
            End If
        End If
      
    Loop
     
    If Hour(currentDateTime) = 13 And Minute(currentDateTime) = 0 Then
        currentDateTime = DateValue(currentDateTime) + TimeValue("12:00:00")
    End If
   
    ' Return the final datetime
    AddWorkingHours = currentDateTime

End Function

see my notes in the comments for checking for a holiday, I consolidated the Ifs a bit and you were comparing a date to a Boolean which would
not work. You were checking startDate = isHoliday, when you just needed If isHoliday.

Also check out the notes on the usage of the Hour function, where you seem to expect it to know what a 1/2 hour is, as it returns "whole number between 0 and 23"
You're comparing it to 8.5 and 12.5, if you want to know if it is greater then 8.5 you need to check hour and minutes of the currentDateTime.

I'm a bit confused as to the loop and why you'd add 12 hours (so 1pm becomes 1am) as the end of the code.

I hope this makes sense, give your code another pass and use debugging to check the values as the code runs. https://www.excel-easy.com/vba/examples/debugging.html