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

  • Thread starter Thread starter N Ramsay
  • Start date Start date
N

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
 
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
 
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
 
Thanks also to Steve and Gary for quick responses.

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

Kind Regards

NR
 
Back
Top