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.

Please Login or Register  to view this content.

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