Results 1 to 3 of 3

Thread: Formula (2000)

  1. #1
    ecuellar
    Guest

    Formula (2000)

    I have a spread sheet (imported from access) which has two date/time stamps in four fields. Approved date, Approved time, Entered date, Entered time. I need a formula which will calculate total hours between the two. But, wait there is a catch.. I need it to eliminate 48 hours for the weekends. Can anyone help a poor excel user who hasn't done formulas since.. dare I say, Lotus 1-2-3 DOS version <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Any help is greatly appriciated.

    Liz

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula (2000)

    If you have the Analysis Toolpak installed in your version of Excel, you can use the NETWORKDAYS() function, which returns the number of work days between two dates. For example =NETWORKDAYS(A1,B1)-1 will return the number of days between the date in A1 (startdate) and the date in B1(end date). You can add another argument which points to a list of holidays, which can then be taken into account as well. Check the help file for full details.

    If you are not sure about the Analysis toolpak, go Tools, Add-Ins, and make sure that the Analysis ToolPak is checked. It should be near the top of your AddIn list. If you cannot find it , you will probably have to install it from your Excel/Office CD.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (2000)

    If ending date is in A1 and ending time is in B1 and start date is in C1 and start time is in D1, then the following formula should give you what you want:

    <pre>=networkdays(C1,A1)*24+(B1-D1)*24
    </pre>


    The networkdays() function is in the Analysis Toolkit addin. If you do not have this addin active, you will have to go to Tools/Addins and activate it. If it is not in the list, you will have to install it from the Excel CD. You can also give networkdays() a list of holidays if you also want them eliminated.
    Legare Coleman

Posting Permissions

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