calculate the number of week days and weekend days in a range in sequencial order
Posted by Tom Malinski on August 05, 2000 7:15 PM
I enter a Start Date in cell A1 and an End Date in cell B1. The date range may span several weeks or more. I need to calculate how many week-days and week-end days are in the range in sequencial order and display them in the same row in adjacent cells (C1, D1, E1, etc, as many as required.) Example: Start Date = 08/08/00, End Date = 09/04/00. calculated values would be 4 2 5 2 5 2 5 2 1). Please Help!!
Posted by Celia on August 05, 0100 10:59 PM
Tom
Put the following formula in Cell C1 :-
=IF(OR(WEEKDAY($A$1)=1,WEEKDAY($A$1)=7),"",IF(7-WEEKDAY($A$1)>$B$1,DATEDIF($A$1,$B$1,"d")+1,7-WEEKDAY($A$1)))
Put the following formula in Cell D1 and drag it to the right as far as required :-
=IF(MOD(COLUMN(),2)=1,IF(DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1)<1,0,IF(DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1)<6,DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1),5)),IF(DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1)<1,0,IF(DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1)<2,DATEDIF($A$1,$B$1,"D")+1-SUM($C$1:C1),2)))
If you would prefer a macro to do it, post again.
Celia
