Another thread that wanted to count "weekends" got me to thinking about this one:
Count the full weeks (from Monday thru Sunday) between 2 dates.
For example...
Data Range
A B C D E F 1 Date Weekday ------ Start End Weeks 2 8/1/2013 Thu 8/1/2013 8/15/2013 1 3 8/2/2013 Fri 4 8/3/2013 Sat 5 8/4/2013 Sun 6 8/5/2013 Mon 7 8/6/2013 Tue 8 8/7/2013 Wed 9 8/8/2013 Thu 10 8/9/2013 Fri 11 8/10/2013 Sat 12 8/11/2013 Sun 13 8/12/2013 Mon 14 8/13/2013 Tue 15 8/14/2013 Wed 16 8/15/2013 Thu
Full Monday thru Sunday weeks = 1
This array formula** entered in F2:
=(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
I'm thinking there has to be a nice short formula that will do this.
daddylonglegs, you out there?
Bookmarks