I'm looking for a function that will track elapsed business hours only. So, it should exclude non-business hours, holidays, and weekends. I've tried the function below, but it fails if the start date/time or end date/time fall outside of business hours. Even though I don't want to track non-business hours, I do have to be able to log them.
=ROUND(IF(NETWORKDAYS(A1,B1,luHolidays)=1,(MIN(luDayEnd,MOD(B1,1))-MAX(luDayStart,MOD(A1,1))),(NETWORKDAYS(A1,B1,luHolidays)-2)*(600/1440)+(luDayEnd-MAX(MOD(A1,1),luDayStart))+MIN(MOD(B1,1),luDayEnd)-luDayStart)*24,0)/24
A1 contains the logged start date and time (e.g. 2006.01.22 7:00 AM)
B1 contains the logged end date and time (e.g. 2006.01.23 10:00 AM)
luHolidays contains the list of holidays
luDayStart contains the beginning of the business day (e.g. 8:00 AM)
luDayEnd contains the end of the business day (e.g. 5:00 PM)
Can any of you experts help me?
Bookmarks