Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
Hello,
Put this worksheet function (the first listed one) into a macro then: http://www.sulprobil.com/html/date_formulas.html Regards, Bernd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
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 -- Gary''s Student - gsnu2007g "N Ramsay" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How many Monday's in a date range?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
On 7 Apr, 17:28, Bernd P wrote:
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: How many Monday's in a date range?
Thanks also to Steve and Gary for quick responses.
Not sure which one to go for, but many thanks to all. Kind Regards NR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert a date to Monday's date within its week? | Excel Worksheet Functions | |||
Need formula to return next Monday's date | Excel Worksheet Functions | |||
Given today's date, I want this week's Monday's date | Excel Worksheet Functions | |||
Display Monday's date only on x-axis | Charts and Charting in Excel | |||
copy date based on date -refer to date range | Excel Programming |