View Full Version : Solved: NETWORKDAYS formula amendment
marshybid
07-02-2008, 02:33 AM
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
07-02-2008, 02:49 AM
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
07-02-2008, 02:57 AM
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
07-02-2008, 03:01 AM
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
07-02-2008, 03:03 AM
Thanks xld,
I have been re-reading your formula since and get the logic now.
Marking as solved.
Marshybid:hi:
Bob Phillips
07-02-2008, 03:07 AM
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
07-02-2008, 03:09 AM
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
07-02-2008, 05:29 AM
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
07-02-2008, 05:55 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.