+ Reply to Thread
Results 1 to 8 of 8

Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function

  1. #1
    richarjb
    Guest

    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....



  2. #2
    Ron Coderre
    Guest

    RE: 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....
    >
    >
    >


  3. #3
    richarjb
    Guest

    RE: 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....
    >>
    >>


  4. #4
    Ron Coderre
    Guest

    RE: 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....
    > >>
    > >>

    >


  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Here's another option which doesn't require Analysis ToolPak

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

  6. #6
    richarjb via OfficeKB.com
    Guest

    RE: 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

  7. #7
    richarjb via OfficeKB.com
    Guest

    Re: 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

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by richarjb via 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?

    formula doesn't require CTRL+SHIFT+ENTER

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1