|
Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function
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.... ;) |
Getting XL to Calc Working Days Lost without using NETWORKDAYS() F
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.... ;) |
Getting XL to Calc Working Days Lost without using NETWORKDAYS() F
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.... ;) |
Getting XL to Calc Working Days Lost without using NETWORKDAYS
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.... ;) |
Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function
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 |
Getting XL to Calc Working Days Lost without using NETWORKDAYS
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 |
Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function
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 |
Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function
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 |
| All times are GMT +1. The time now is 02:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com