Has any one got a formulae for calculating working days lost between two
dates without using the NETWORKDAYS() Function. The various attempts I have
made bring in very subtle variances and I have tried everything - I think....

;)

Can you define what you mean by "working days lost"?

Can you post one of your formulas so we can see what you've tried?

***********
Regards,
Ron

XL2002, WinXP


"richarjb" wrote:

Has any one got a formulae for calculating working days lost between two
dates without using the NETWORKDAYS() Function. The various attempts I have
made bring in very subtle variances and I have tried everything - I think....

;)

Hi Ron,

Working Days Lost is for the number of days lost when someone has been absent
from work for a specified period. You can have total days but HR normally
want to know the actual days lost that a person would normally work ie Mon to
Fri not including weekends.

My Example for Working Days Lost: -

A1 = 01/04/2006 'Absence Started
B1 = 30/04/2006 'Absence Ended

C1 = B1-A1+1 = 30.00 'Total Days between Start and End
D1 = C1/7 = 4.29 'Return no of Weeks in period
E1 = INT(D1)*5 = 20.00 'Convert Weeks (in D1) to integer multiply
by 5 working days in week
F1 = MOD(D1,2) = 0.29 'Get the Fraction of days in Week

G1 = F1/(1/7) = 2.00 'Convert Fraction to Work Days

H1 = E1+H1 = 22.00 'Total Working Days in period *** in
theory

Now check this against a Calendar and the truth is it's 20. So do I need add
something here to compensate?

Cheers



Ron Coderre wrote:
Can you define what you mean by "working days lost"?

Can you post one of your formulas so we can see what you've tried?

***********
Regards,
Ron

XL2002, WinXP

Has any one got a formulae for calculating working days lost between two
dates without using the NETWORKDAYS() Function. The various attempts I have
made bring in very subtle variances and I have tried everything - I think....

;)

To count the number of workdays lost...try one of these:

For
A1: (startdate)
B1: (enddate)

This one requires the Analysis ToolPak add-in to be enabled:
C1: =NETWORKDAYS(A1,B1)

Note: there is a 3rd argument in the NETWORKDAYS function for a list of
holidays that you don't want to count as missed workdays. See Excel Help for
more information.

OR
This function does not requer the Analysis ToolPak add-in:
C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),2)<6))

Change the range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"richarjb" wrote:

Hi Ron,

Working Days Lost is for the number of days lost when someone has been absent
from work for a specified period. You can have total days but HR normally
want to know the actual days lost that a person would normally work ie Mon to
Fri not including weekends.

My Example for Working Days Lost: -

A1 = 01/04/2006 'Absence Started
B1 = 30/04/2006 'Absence Ended

C1 = B1-A1+1 = 30.00 'Total Days between Start and End
D1 = C1/7 = 4.29 'Return no of Weeks in period
E1 = INT(D1)*5 = 20.00 'Convert Weeks (in D1) to integer multiply
by 5 working days in week
F1 = MOD(D1,2) = 0.29 'Get the Fraction of days in Week

G1 = F1/(1/7) = 2.00 'Convert Fraction to Work Days

H1 = E1+H1 = 22.00 'Total Working Days in period *** in
theory

Now check this against a Calendar and the truth is it's 20. So do I need add
something here to compensate?

Cheers



Ron Coderre wrote:
Can you define what you mean by "working days lost"?

Can you post one of your formulas so we can see what you've tried?

***********
Regards,
Ron

XL2002, WinXP

Has any one got a formulae for calculating working days lost between two
dates without using the NETWORKDAYS() Function. The various attempts I have
made bring in very subtle variances and I have tried everything - I think....

;)

Here's another option which doesn't require Analysis ToolPak

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=543692

Thanks Ron,

Yes it is. I think I can see what it's doing

Ron Coderre wrote:
To count the number of workdays lost...try one of these:

For
A1: (startdate)
B1: (enddate)

This one requires the Analysis ToolPak add-in to be enabled:
C1: =NETWORKDAYS(A1,B1)

Note: there is a 3rd argument in the NETWORKDAYS function for a list of
holidays that you don't want to count as missed workdays. See Excel Help for
more information.

OR
This function does not requer the Analysis ToolPak add-in:
C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),2)<6))

Change the range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

[quoted text clipped - 39 lines]

;)

--
Message posted via http://www.officekb.com

Thanks DLL

Is that an array in there within the braces?


daddylonglegs wrote:
Here's another option which doesn't require Analysis ToolPak

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200605/1

richarjb via OfficeKB.com Wrote:
Thanks DLL

Is that an array in there within the braces?


daddylonglegs wrote:
Here's another option which doesn't require Analysis ToolPak

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200605/1

Yes it is - why do you ask?

formula doesn't require CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=543692