I have a sheet with Starting and Ending dates. i want to find How many Mondays, Tue, Wed, Thu, Fri, Sat and Sundays fallen between two dates. So i wrote the following function. it works fine but 1st day of month is skipping.
=ABS(INT((N($B$5)-L10)/7)-INT((N($B$4)-L10)/7)) 'B4- Starting date and B5- Ending date
for example : 1/10/2021 and 31/10/201 starting and ending dates.
as per above function Total Mondays-4, Tue-4, Wed-4, Thu-4, Fri-4, Sat-5 and Sun-5
but originally Mon-4, Tue-4, Wed-4. Thu-4, Fri-5, Sat-5, Sun-5.
What happened here is 1st October, 2021 is a FRIDAY.it skips first day of a month.
any help... tqs in advance...
CodePudding user response:
You could use what I call a "brute force" method, where you create an array of all the relevant days, and check the matches:
=SUMPRODUCT(N(WEEKDAY(ROW(INDEX($A:$A,$B$4):INDEX($A:$A,$B$5)))=L10))
Since you asked for a VBA solution, here's one way:
Mnth can be any date in a month
DOW is the day of the week where 1=Sunday
The function uses the same algorithm -- testing each date in the range to see if it matches the desired day of week.
Option Explicit
Function weekdaysInMonth(Mnth As Date, Optional DOW As Long = vbSunday) As Long
Dim I As Long
Dim dStart As Date, dEnd As Date
Dim Cnt As Long
dStart = DateSerial(Year(Mnth), Month(Mnth), 1)
dEnd = DateSerial(Year(Mnth), Month(Mnth) 1, 0)
For I = dStart To dEnd
If Weekday(I) = DOW Then Cnt = Cnt 1
Next I
weekdaysInMonth = Cnt
End Function