UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Number of Mondays in a Date Range    
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

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 2,657 times.  This page was last modified 22:16, 20 January 2015 by dflak.   Disclaimers