Calculate time between start time and end time

[girishtare]

Hi,

I have a major road block.

the vendor has a 21 hr working window; start from 7am and goes until 4am; Mon to Fri.

Here is a scenario:
- i request for a product information from a vendor on 3-Feb-14 8:00am (Monday)
- he replies with all of the product info on 6-Feb-14 12:00pm (Thursday)

can you find the time in above scenario consideration the working window?


Here is another scenario:
- i request for a product information from a vendor on 6-Feb-14 8:00am (Thursday)
- he replies with all of the product info on 11-Feb-14 12:00pm (Tuesday)
- Sat & Sun are days off but keep in mind that my Friday shift ends on sat at 4am so the networdays formula wont work.

can you find the time in above scenario consideration the working window?

Thanks,
Girish Tare

[Kaper]

for the computations - move your days into standard weekdays. As you are calculating time difference it is the same if you take just b-a or (b-c)-(a-c)

So for both start and end date do not use their standard value, but:
=A1-4/24
this way, for instance for start you will have
6-Feb-14 4:00am (Thursday)
And then normal use of networkdays is fine.


second suggestion - as you are using ex2010 check out new function - networkdays.intl

[daddylonglegs]

Following Kaper's suggestion, assuming you have start time/date in A2 and end time/date in B2, and those will always be within working hours, you can use this formula to get the total working hours between the two "timestamps"

=(NETWORKDAYS(A2-"4:00",B2-"4:00")-1)*"21:00"+MOD(B2-"4:00",1)-MOD(A2-"4:00",1)

custom format result cell as [h]:mm and you'll get 67:00 for both of your examples