UDF: How many Monday's in a date range?

[N Ramsay]

Hi,

Newbie help needed please

I need to write 5 UDF's in Excel.

They are all similar, so let's explain the first one.

I'll pass in 2 parameters: "startdate" and "enddate".

I want to calculate the number of Mondays that exist in the date range
between startdate and enddate, and pass it back.

The other 4 UDF's are for Tues, Wed, Thurs & Fri.

Any thoughts please?

Kind Regards,

NR

 

[Bernd P]

Hello,

Put this worksheet function (the first listed one) into a macro then:
http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

 

[Gary''s Student]

For Monday:

Function mondaymonday(r1 As Range, r2 As Range) As Integer
For i = r1.Value To r2.Value
If Weekday(i) = 2 Then
mondaymonday = mondaymonday + 1
End If
Next
End Function

So if A12 contains 4/6/2008
and A13 contains 4/14/2008
then =mondaymonday(A12,A13) will return:
2

 

[Steve]

See below ....

Steve

Count The Number Of Sundays Between Two Dates



Public Function HowManyWD(FromDate As Date, ToDate As Date, WD As Long)
HowManyWD = DateDiff("ww", FromDate, ToDate, WD) _
- Int(WD = WeekDay(FromDate))
End Function

Example:
HowManyWD(7/1/03,7/31/03/,1) Returns the number of Sundays in July 2003

Note: Change the 1 to the following to find other days:
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thursday
6 - Friday
7 - Saturday

 

[N Ramsay]

Hello,

Put this worksheet function (the first listed one) into a macro then:http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

Bernd, that's perfect!

Many Thanks indeed.

 

[Joel]

It is not very complicated. Lets see if I can explain. You subtract the two
dates. You need to make an adjustment to make the start date a Monday. but
because Weekday function return 1 for the first day you need to subtract an
additional one

Sayy yoiur start date was tomorrow tuesday and the end date was Wednesday

then you pretend these dates were Monday and Tuesday and you count your week
from this new Monday. but Monday from the Weekday function is 1 so you
subtract one from the number.


Function mondays(startdate, enddate)
Startday = Weekday(startdate, vbMonday)

mondays = Int((enddate - startdate - (Startday - 1)) / 7)

End Function

 

[N Ramsay]

Thanks also to Steve and Gary for quick responses.

Not sure which one to go for, but many thanks to all.

Kind Regards

NR