Hi,
here is a formula that will take hours into account:
=IF(AND(NOT(D5-C5=0),D5-C5<1),(D5-C5)*24/8,INT(D5-C5)+1-SUM(IF(ISERROR(MATCH(ROW(INDIRECT(C5&":"&D5)),Holidays,0)),0,1))-SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(C5&":"&D5))),ExcludeDaysOfWeek,0)),0,1)))
Remember to press "CTRL" + "SHIFT" + Enter when you copy this formula to make it an ARRAY formula.
Here I assume that a partial day vacation is taken within the same day so you can enter the start hour and end hour of this vacation. The formula will not work if you enter hours in a vacation longer than a day.
Hours are calculated based on an 8 hors shift, so 4 hours is 0.5 day.
If a long vacation is started 4 hours before the actual day (early leave), you will have to enter this on 2 rows. One for the hours and the other for the days.
Hope this helps.
Bookmarks