Works: Calculating Number of Working Days Between Two Dates
Article ID: 114946
Article Last Modified on 11/15/2004
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
This article was previously published under Q114946
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).
Additional query words: w_works kbhowto 3.00 3.00a 2.00 2.00a 4.00 4.00a 4.50 4.50a calculate Monday Tuesday Wednesday Thursday Friday Wrk week-day
Keywords: kbinfo KB114946