Excel, the relevant calculation days

 In Excel, by subtract two date values, you can get the interval between these two dates the specific number of days; and a date value that the number of days with a constant phase addition and subtraction, you can get a date from the X days of the date of value. Such as: "2000/05/31" - "2000/05/01" be 31, "2000/05/01" and 90 are "2000/07/30." This is the Excel provides a simple but very useful although the date of computing.
    However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
    A, networkdays (Start_date, End_date, Holidays) function
    networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
    networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
    In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
    Second, workday (Start_date, Days, Holidays) function
    workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
    Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
    In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
    The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
    Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
    Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.
        Click here to download the content described in this article related to the template file 13K "-" 2000/05/01 "be 31," 2000/05/01 "and 90 are" 2000/07/30. "This is to provide an Excel Although the simple but very useful date calculation functions.
    However, this feature limitations in the practical work, making it unable to meet the needs of a higher level, especially the "working day" concept is increasingly widely used in various fields today, a lot of work need to be related calculations. The most common number of days such as staff attendance, delivery of the calculation, the calculation of the date of payment and so on. Excel users how these needs the solution?
    A, networkdays (Start_date, End_date, Holidays) function
    networkdays () function is designed to calculate the full two days between the date of numerical values. This value will not include weekend days and other specifically designated holidays.
    networkdays () function takes three arguments: Start_date said the start date, End_date as the termination date, Holidays, said the holiday as one or more specific date. These values can either be entered manually, or you can reference the values of the cell. May the following year to calculate the number of working days, for example, specific description networkdays () functions:
    In B1, enter the start date "2000/05/01", in B3, enter the date of termination, "2000/05/31", in E2, E3, type of statutory holidays, "2000/05/01" and "2000/05 / 02. " By the formula "= NETWORKDAYS (B2, B4, E2: E3)" we can easily calculate the number of working days in May was. Of course, we can directly enter the formula "= NETWORKDAYS (" 2000-5-1 "," 2000-6-1 ", {" 2000-5-1 "," 2000-5-2 "})" to get the results.
    Second, workday (Start_date, Days, Holidays) function
    workday () function is used to calculate a date (start date) working days before or after a date specified separated by a date value. It also takes three arguments: Start_date said the start date, Days before or after the Start_date excluding weekends and holidays the number of days. Days will have a positive future date; is negative over the past generation date, Holidays, said the holiday as one or more specific date.
    Suppose we have May 1 with the customer signed a purchase and sales contracts, the contract 90 days after delivery. So, how should we calculate in the end day shipment?
    In B1, enter the start date "2000/05/01", B3, enter the contract in time "90", in the E2, E3, type of statutory holidays, "2000/05/01" and "2000/05/02." By the formula "= WORKDAY (B2, B4, E2: E3)", we can know the delivery date is "2000/09/05." Above, we can directly enter the formula "= WORKDAY (" 2000-5-1 ", 90, {" 2000-5-1 "," 2000-5-2 "})" to be calculated.
    The wait has been to try to open Excel's friends may be crying, and you say here how these two functions I do not ah? Do not worry, click "Tools" menu in the "add" and find "Analysis ToolPak" and give it a Komagari, and then determine, OK! It turned out that the two functions are embedded in the "Analysis ToolPak" add this extension function in Excel, but Excel is not in default load the add-in.
    Another problem worth noting: the date that the cell number format set to "date" type, while the number of days the cells that do not set the "date" type, or you may see "1900/02 / 21 "or" 36774 "such a strange outcome yo!
    Described above is the basic usage of these two functions, we can combine the best of the actual situation of their work, giving top priority to complete computing tasks more efficiently.