Welcome Guest ( Log In | Register )

 @import url(https://www.google.com/cse/api/branding.css); Custom Search
Menu
Content Home Articles Function Library Class Library API Declarations Error Codes Featured Content

Resources Wiki FAQ Wiki Help Markup Listing Create Article Guidelines Templates

To Do Completion Required Review Required Wanted Pages Dead End Pages

Toolbox What links here Related changes Upload file Special pages Printable version Permanent link

Advertisment Number of Mondays in a Date Range Options Number of Mondays in a Date Range

This formula was created using a number of helper cells and "collapsing" them into a single formula

In this formula
End_Date is the ending date of the period
Start_Date is the starting date of the period
WD is the weekday (Sun = 1, Mon = 2, Tue =3, Wed = 4, Thu = 5, Fri = 6, Sat = 7).

CODE

=INT((End_Date-Start_Date+1)/7)+IF(WEEKDAY(Start_Date)<=WEEKDAY(End_Date),IF(AND(WD>=WEEKDAY(Start_Date),WD<=WEEKDAY(End_Date)),1,0), IF(OR(WD>=WEEKDAY(Start_Date),WD<=WEEKDAY(End_Date)),1,0))-IF(MOD((End_Date-Start_Date+1),7)=0,1,0)

This is a regular formula, not an array formula.

Here is a small example of the file in action: media:How Many Mondays.zip

 @import url(https://www.google.com/cse/api/branding.css); Custom Search This page has been accessed 2,657 times.  This page was last modified 22:16, 20 January 2015 by dflak.   Disclaimers