Thanks in advance for any help that can be offered.
I need to calculate the time between Assign date & Owned Date, but i must exclude weekends, if exists, and non business hours.
I am using ths formula at G column :
=(NETWORKDAYS(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);E2)-1+MOD(E2;1)-MOD(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);1))
It exclude weekends and start counting from Next business day from 8:00.
But i have different support hours, as example line 9, so i need to modify my formula with business hours in columns B & C.
I tried this formula in column H
=(NETWORKDAYS(D2;E2)-1)*(C2-B2)+IF(NETWORKDAYS(E2;E2);MEDIAN(MOD(E2;1);C2;B2);C2)-MEDIAN(NETWORKDAYS(D2;D2)*MOD(D2;1);C2;B2)
but i can't find out how change it in order start counting from Next business day. As example it gives me wrong result in cell H3.
I am confused.
Please help!
I need to calculate the time between Assign date & Owned Date, but i must exclude weekends, if exists, and non business hours.
I am using ths formula at G column :
=(NETWORKDAYS(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);E2)-1+MOD(E2;1)-MOD(IF(WEEKDAY(D2;2)>5;WORKDAY(D2;1)+1/3;D2);1))
It exclude weekends and start counting from Next business day from 8:00.
But i have different support hours, as example line 9, so i need to modify my formula with business hours in columns B & C.
I tried this formula in column H
=(NETWORKDAYS(D2;E2)-1)*(C2-B2)+IF(NETWORKDAYS(E2;E2);MEDIAN(MOD(E2;1);C2;B2);C2)-MEDIAN(NETWORKDAYS(D2;D2)*MOD(D2;1);C2;B2)
but i can't find out how change it in order start counting from Next business day. As example it gives me wrong result in cell H3.
I am confused.
Please help!