Calculate time (excluding weekend (48 hrs))

[Guest]

Need help to calculate the total number of hours excluding the 48 hrs of a
weekend.

Currently my spreadsheet looks like this:

C3 (date/time) start
D3 (date/time) end
E3 number value total hours (=(D3-C3)*24)

Grateful for any help.

 

[Guest]

=NETWORKDAYS(C3,C4)*24

 

[Guest]

Sorry Mike.....

C3 is the start date/time of a particular project
C4 is the end date/time of that particular piece of work
D4 calculates the hours it has taken to complete that piece of work - I need
D4 to ALSO ignore the 48 hours in a weekend.

Can you help???

 

[Guest]

I have assumed youv'e entered the day and time using CTRL ; and CTRL + Shift
+ ;

so try
C3 = 02/02/2007 08:00:00
C4 = 05/02/2007 09:26:00

=((C4-C3)*24)-(NETWORKDAYS(C3,C4)*24)

This returns in D4 25.43

 

[Roger Govier]

Hi Mike

I think this only works in the specific case of the dates the OP used
and would return incorrect results if for example the second date were
increased to 06/02/07 where your result remains fixed at 25.43. This is
because you are taking working days away from total elapsed time, rather
than the reciprocal of deducting just weekend days from the elapsed
time.

I would suggest the following
=((C4-C3)*24)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(C3)&":"&INT(C4))),2)>5))*24

The second part of the formula is counting the number of days that are
weekend days during the total time interval, multiplying this by 24 and
subtracting from the total elapsed time.

 

[Guest]

I get value: #NAME? :-( Obviously something is not set right (?)

 

[Roger Govier]

Hi Andy

The #NAME is because Networkdays is part of the Analysis Toolpak.
You need to go Tools>Addins>and click the Analysis Toolpak.

However, as i pointed out in an earlier posting, I don't think this
proposed solution provides the correct answer in all circumstances.

 

[Guest]

Roger: need some help setting this up.....

I have Data Analysis in the Tools menu and then it gives me a list and from
there I cannot determine which option to select and setup...

I appreciate this may not be the answer but the function will be good enough
for what we want it to do.

Can you please help?

 

[Bob Phillips]

It is not Data Analysis that you need, it is the Analysis Toolpak.

=NETWORKDAYS(C3+1,D3-1)*24+1-MOD(C3,1)+MOD(D3,1)

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

E3 =(NETWORKDAYS(C3,D3)-1+MOD(D3,1)-MOD(C3,1))*24

 

[Guest]

Hello Andy,

Can C3 or D3 be at the weekend?