Work Days and Holiday Hours

[DarrenF]

Hello,

I have created the below formula, which I'm trying to remove weekends and company holidays. I have a table built on another worksheet (worksheet name = Workings). For some reason it's not removing the time even though I have the date in the table.

=IF(H2="","",IF(G2<>"CS Tech Team Queue","",((NETWORKDAYS(F2,H2,holidaylist)-1)*("17:00"-"5:00")+IF(NETWORKDAYS(H2,H2,holidaylist),MEDIAN(MOD(H2,1),"17:00","5:00"),"17:00")-MEDIAN(NETWORKDAYS(F2,F2,holidaylist)*MOD(F2,1),"17:00","5:00"))*24))

 

[DarrenF]

Narrowed it down to it not recognizing all the dates in my "holidaylist" table. I have 2016 and 2017 working fine, but when I added 2018 it doesn't recognize it. If I paste the dates into 2016 or 2017, it works fine. But it won't let me extend the list.

 

[shg]

If holidaylist is a static range, you need to extend it to include the additional dates. Or make it a dynamic range.