Works: Calculating Number of Working Days Between Two Dates

Article translations Article translations
Article ID: 114946 - View products that this article applies to.
This article was previously published under Q114946
Expand all | Collapse all

On This Page

SUMMARY

You can calculate the number of workdays between two arbitrary dates by using the following formula in conjunction with the days left table shown below
=Int((EndDay-StartDay)/7)*5+VlookUp(Mod((EndDay-StartDay),7),A3:H9, Choose(Mod(StartDay,7),1,2,3,4,5,6,7))
where StartDay is the starting date and EndDay is the ending date of the time span you are trying to find the number of working days for.

DAYS LEFT TABLE

The Days Left table uses cells A3:H9 as shown:
        A      B    C    D    E    F     G    H
 1                  Day Started
 2 Days Left  Sat  Sun  Mon  Tue  Wed  Thur  Fri
 3      0      0    0    0    0    0     0    0
 4      1      0    1    1    1    1     1    0
 5      2      1    2    2    2    2     1    0
 6      3      2    3    3    3    2     1    1
 7      4      3    4    4    3    2     2    2
 8      5      4    5    4    3    3     3    3
 9      6      5    5    4    4    4     4    4
				

MORE INFORMATION

The formula works because any seven days will always contain five working days, which is calculated by "Int((EndDay-StartDay)/7)*5". Then the Days Left table indicates how many working days are left over (after subtracting all the full seven-day weeks), depending on what day of the week the StartDay is.

NOTE: The Days Left table could have just as easily been based on the EndDay (then the EndDay would be used in the Choose function and the values in the table would be different).

Properties

Article ID: 114946 - Last Review: November 15, 2004 - Revision: 4.0
APPLIES TO
  • Microsoft Works 2.0 Standard Edition
  • Microsoft Works 2.0a
  • Microsoft Works 3.0 Standard Edition
  • Microsoft Works 3.0a
  • Microsoft Works 4.5 Standard Edition
  • Microsoft Works 4.5a
  • Microsoft Works 4.0 Standard Edition
  • Microsoft Works 4.0a
Keywords: 
kbinfo KB114946
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com