Filtering out weekday dead time

[Tony Clarke]

I am using NETWORKDAYS function to determine the number of weekdays between
two dates e.g 01/03/06 to 31/03/06 =networkdays(A6,B6) gives the result 23.
However i now need to detract the number of "dead hours and minutes "
incurred during those 23 week days between the hours of 1800hrs to 0800hrs
to give the total number of live hours and minutes incurred 0800hrs to
1800hrs on weekdays.

Can you help ??

[daddylonglegs]

If A6 and B6 just contain dates

=NETWORKDAYS(A6,B6)*("18:00"-"08:00")

note this will represent hours from 08:00 on A6 to 18:00 on B6

or if A6 and B6 contain dates/times

=(NETWORKDAYS(A6,B6)-1)*("18:00"-"08:00")+MOD(B6,1)-MOD(A6,1)

in both cases format result cell as [h]:mm

[Difficult1]

Well, there are 10 hours between 0800 and 1800 hrs.... so, your net workdays
x 10 would give you the number of live hours. =networkdays(a6,b6)*10

"Tony Clarke" wrote:

> I am using NETWORKDAYS function to determine the number of weekdays between
> two dates e.g 01/03/06 to 31/03/06 =networkdays(A6,B6) gives the result 23.
> However i now need to detract the number of "dead hours and minutes "
> incurred during those 23 week days between the hours of 1800hrs to 0800hrs
> to give the total number of live hours and minutes incurred 0800hrs to
> 1800hrs on weekdays.
>
> Can you help ??

[Tony Clarke]

Many thanks daddylonglegs, i'll give it a try.

"Tony Clarke" wrote:

> I am using NETWORKDAYS function to determine the number of weekdays between
> two dates e.g 01/03/06 to 31/03/06 =networkdays(A6,B6) gives the result 23.
> However i now need to detract the number of "dead hours and minutes "
> incurred during those 23 week days between the hours of 1800hrs to 0800hrs
> to give the total number of live hours and minutes incurred 0800hrs to
> 1800hrs on weekdays.
>
> Can you help ??

[Tony Clarke]

Hi , i tried these date and time combinations and all work great except those
combinations that have a start time before 0800hrs, as the calculation should
not be including any hours incurred before 0800hrs
(see example lines 1,2 and 3 for errors whereas the calculation works great
for lines 4,5,6)
Is it possible to tell the calculation to ignore any time before 0800hrs on
the start date ??

Start Date time End Date Time Calculation
01/03/2006 05:00 03/03/2006 15:00 30:00
01/03/2006 06:00 03/03/2006 15:00 29:00
01/03/2006 07:00 03/03/2006 15:00 28:00
01/03/2006 08:00 03/03/2006 15:00 27:00
01/03/2006 09:00 03/03/2006 15:00 26:00
01/03/2006 10:00 03/03/2006 15:00 25:00

regards Tony C
--------------------------------
"daddylonglegs" wrote:

>
> If A6 and B6 just contain dates
>
> =NETWORKDAYS(A6,B6)*("18:00"-"08:00")
>
> note this will represent hours from 08:00 on A6 to 18:00 on B6
>
> or if A6 and B6 contain dates/times
>
> =(NETWORKDAYS(A6,B6)-1)*("18:00"-"08:00")+MOD(B6,1)-MOD(A6,1)
>
> in both cases format result cell as [h]:mm
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=525903
>
> .
>

[daddylonglegs]

I was assuming that your start time/date and end time/date would be within work hours. If not you probably need to use a more complex formula, i.e.

=(NETWORKDAYS(A2,B2,)-1)*($E$5-$E$4)+IF(WEEKDAY(B2,2)>5,$E$5,MEDIAN(MOD(B2,1),$E$5,$E$4))-IF(WEEKDAY(A2,2)>5,$E$4,MEDIAN(MOD(A2,1),$E$5,$E$4))

where A2 contains start time/date, B2 contains end time/date, E4 contains daily start time (e.g. 08:00) and E5 contains daily end time (e.g. 18:00). This allows for your time/dates in A2 and B2 to be any time, even at weekends

[Tony Clarke]

Thats done the trick, most grateful for your help !!

Regards Tony C
------------------------------

"daddylonglegs" wrote:

>
> I was assuming that your start time/date and end time/date would be
> within work hours. If not you probably need to use a more complex
> formula, i.e.
>
> =(NETWORKDAYS(A2,B2,)-1)*($E$5-$E$4)+IF(WEEKDAY(B2,2)>5,$E$5,MEDIAN(MOD(B2,1),$E$5,$E$4))-IF(WEEKDAY(A2,2)>5,$E$4,MEDIAN(MOD(A2,1),$E$5,$E$4))
>
> where A2 contains start time/date, B2 contains end time/date, E4
> contains daily start time (e.g. 08:00) and E5 contains daily end time
> (e.g. 18:00). This allows for your time/dates in A2 and B2 to be any
> time, even at weekends
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=525903
>
>