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....
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))
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
Yes it is - why do you ask?Originally Posted by richarjb via OfficeKB.com
formula doesn't require CTRL+SHIFT+ENTER
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks