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]

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

 

[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]

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

 

[watermt]

Good afternoon,
I've been trying to use your formula but it appears not to work on cells
that have no date/time entered. Is there a way to get the empty cells
ignored and give me a correct total (SUM), see example below:

Cell D13 = 10/13/2008 9:00:00 PM
Cell F13 = 10/13/2008 9:30:00 PM
=(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1)

Cell N13 = 00:30
=SUM(N13:OFFSET(N21,-1,0))

Cell D14 = <blank>
Cell F14 = <blank>
=(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1)
Cell N14 = ############## (indicating negative dates or times)

I'm trying to total a list of these results from cell N13 through cell N21
by using the following formula:
=SUM(N13:OFFSET(N21,-1,0))

When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect,
it should equal 48:30. Cell formats are [h]:mm and my cell values are as
follows:
N13 = 00:30
N14 = 02:15
N15 = 02:00
N16 = ######
N17 = ######
N18 = ######
N19 = 43:45
N20 = ######

Any guidance on my dilemma is greatly appreciated!