Hi all,
Firstly, thank you for your help.
I have a data set of entry and response time. The entry and response time can start/end over the weekend. I'm looking for a formula that can calculate the working hours between the two dates. If the entry date is during working hours and response time is over the weekend, then the hour should stop counting at 5 pm the previous Friday. If the entry date is over weekend and response time is over the weekend, then it should be 0. If the entry date is over the weekend and response is during working hours, it should count only the working hours.
Currently, I have =NETWORKDAYS(H2,I2,Sheet1!$B$2:$B$9)-1-MOD(H2,1)+MOD(I2,1) but it seems to be miscounting Fridays and ALL of the hours on the working day (00:00 - 23:59) instead of from 9 am to 5 pm.
Firstly, thank you for your help.
I have a data set of entry and response time. The entry and response time can start/end over the weekend. I'm looking for a formula that can calculate the working hours between the two dates. If the entry date is during working hours and response time is over the weekend, then the hour should stop counting at 5 pm the previous Friday. If the entry date is over weekend and response time is over the weekend, then it should be 0. If the entry date is over the weekend and response is during working hours, it should count only the working hours.
Currently, I have =NETWORKDAYS(H2,I2,Sheet1!$B$2:$B$9)-1-MOD(H2,1)+MOD(I2,1) but it seems to be miscounting Fridays and ALL of the hours on the working day (00:00 - 23:59) instead of from 9 am to 5 pm.
