VBA Count days in a month (but a bit trickier!)

[ECEK]

When I open my form I have two dates:
A bound date and an unbound date that is seven days previous.

What Im trying to do is to count how many days between these two dates have the same month as the bound date.
eg
Bound Form: 04/11/2018
Unbound(onOpen): 29/10/2018
Calculation = 4

This will then give me the opportunity to create a flag "Please be aware that there are only [4] days in this week"

I can create a flag to say "Please be aware that this is not a full week" but I'd prefer to be specific.

any pointers are gratefully received.
Cheers

 

[Minty]

Just to make sure I have grasped the logic, would the following describe it something like (pseudo code)

Is the -7 date in the same month as [BoundDate] then Calc = 7  ' It has to as you've already calculated it
Else
Day(BoundDate)   ' will return the number of days in the of the BoundDate

If not maybe post up sample data and your expected result?

 

[Gasman]

Not elegant, but it will do the job?

Function TestDays(pdtStart As Date, pdtEnd As Date)
Dim dtStart As Date, dtEnd As Date
Dim i As Integer, iCnt As Integer, iDays As Integer

iDays = pdtEnd - pdtStart + 1
For i = 1 To iDays
    If Month(pdtStart) = Month(pdtEnd) Then
        iCnt = iCnt + 1
    End If
    pdtStart = pdtStart + 1
Next
MsgBox iCnt
TestDays = iCnt
End Function

 

[plog]

Paste this into a module and it should give you the number you want:

Function get_DaysInMonth(in_Date)
   ' determines number of days in_Date is away from first of the month, with a maximum of 7
   
   ret = DateDiff("d", DateSerial(Year(in_Date), Month(in_Date), 1), in_Date) + 1
   ' gets days between submitted date and first of month
   
   If ret > 7 Then ret = 7
   ' limits amount to just 7
   
   get_DaysInMonth = ret
   
End Function

You would pass it just the bound date--it can figure out the rest.

 

[Minty]

Actually, having had another coffee, can't we just use ?

Iif (Day(BoundDate)>=7, 7 ,Day(BoundDate))

 

[plog]

Minty for the win.