Solved: NETWORKDAYS formula amendment

[marshybid]

Morning All,

I am using a NETWORKDAYS formula as part of a larger macro.

The query I have is;

I only want to count Mon - Fri (NETWORKDAYS) between the start and end date, but I also need to only count time between 8AM and 6PM??

Is it possible to do this??

Example:

Cell A1 = 06/01/2008 09:20:45
Cell B2 = 06/03/2008 11:20:53

Result in time taken should = 0:21:00:ss (format "d:hh:mm:ss)

Thanks,

Marshybid

[Bob Phillips]

I make it 15:20

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<5)-(WEEKDAY(A2,2)<5))/2+
(("18:00"-MIN("18:00",MOD(A1,1)))*(WEEKDAY(A1,2)<5))+
((MAX("8:00",MOD(A2,1))-"8:00")*(WEEKDAY(A2,2)<5))

[marshybid]

I make it 15:20

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<5)-(WEEKDAY(A2,2)<5))/2+
(("18:00"-MIN("18:00",MOD(A1,1)))*(WEEKDAY(A1,2)<5))+
((MAX("8:00",MOD(A2,1))-"8:00")*(WEEKDAY(A2,2)<5))

Hi xld, doesn't the NETWORKDAYS formula only count 5 days per week anyway???

I'm not sure I understand the <5 references in your formula above??

Marshybid

[Bob Phillips]

It does, but I am out-selecting the start and end days ... IF they are working days, that is if Weekday(cell,2)<5.

Actually, that is a slight error, it should be <=5

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<=5)-(WEEKDAY(A2,2)<=5))/2+
(("18:00"-MIN("18:00",MOD(A1,1)))*(WEEKDAY(A1,2)<=5))+
((MAX("8:00",MOD(A2,1))-"8:00")*(WEEKDAY(A2,2)<=5))

[marshybid]

Thanks xld,

I have been re-reading your formula since and get the logic now.

Marking as solved.

Marshybid:hi:

[Bob Phillips]

I'm not haviong a good day, there are 10 hours between 8AM and 6PM not 12

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<=5)-(WEEKDAY(A2,2)<=5))*10/24+
(("18:00"-MIN("18:00",MOD(A1,1)))*(WEEKDAY(A1,2)<=5))+
((MAX("8:00",MOD(A2,1))-"8:00")*(WEEKDAY(A2,2)<=5))

which means I make it 13:20 not 15:20

[Bob Phillips]

Thanks xld,

I have been re-reading your formula since and get the logic now.

Marking as solved.

Marshybid:hi:

See my latest post.

[RichardSchollar]

I'm not haviong a good day, there are 10 hours between 8AM and 6PM not 12



You should try working where i do - while it may only be 10 hours, it feels considerably longer than 12!

Richard

[Bob Phillips]

You should try working where i do - while it may only be 10 hours, it feels considerably longer than 12!

Richard

Well if you will use obscure SUMPRODUCT techniques, it is hardly surprising.