Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Business Days (Excel 2000)

    My work sheet has three columns. Col_1 is the mail date and that date is beginning of day. Col_2 is due the due date, that date is also beginning of day. Col_3 is an indicator column, manual input, that lets me know the number of business days for the transaction. The codes are 2Bus, 3Bus, 4Bus, etc. For example, if I mail a letter today, 11/02/2003, for a 2Bus transaction, the due date is Wednesday, beginning of day. Need to find a way to count the number of business days. I have tried using the Excel help but it looks like the formulae are geared for calendar day calculations.

    Thanks - GCB

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Business Days (Excel 2000)

    Install the Analysis Toolpack and you can use =NETWORKDAYS(startdate,enddate,rangelistingholiday s)
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Business Days (Excel 2000)

    You want to use the WorkDay Function that is included in the Analysis Toolpac addin that comes with Excel. If you don't have the Analysis Toolpac installed, you will need to install it and activate the addin before using this function. The following is the Help for this function:

    <hr>WORKDAY
    See Also

    Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. To view the number as a date, click Cells on the Format menu, click Date in the Category box, and then click a date format in the Type box.

    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

    How?

    Syntax

    WORKDAY(start_date,days,holidays)

    Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998"))

    Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

    Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. Learn about array constants. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

    Remarks

    Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.


    If start_date is not a valid date, WORKDAY returns the #NUM! error value.


    If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.


    If days is not an integer, it is truncated.

    Examples

    WORKDAY(DATEVALUE("01/03/1998"),5) equals 35804 or January 9, 1998.

    If January 7, 1998, and January 8, 1998, are holidays, then:
    WORKDAY(DATEVALUE("01/03/1998"),5,{35802,35803}) equals 35808 or January 13, 1998.

    <hr>
    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
  •