A couple of observations, without changing your code too drastically.
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
Bookmarks