DateDiff Count Number of occurrences of each month, weekly between dates-CodePudding

I have some resourcing data based on estimations showing me hours per month for each "role" assigned to a task.

I need to be able to count the number of times a "month" appears in a weekly count between two dates. So i can split the hours accordingly.

i.e between 02-Oct-21 and 27-Nov-21 there is 9 weeks, output from the below code:

Public Sub dtConv()

Dim stDt, enDt As Date

stDt = Sheets("New Job Template").Range("F1").Value
enDt = Sheets("New Job Template").Range("H1").Value

Debug.Print (DateDiff("ww", stDt, enDt))

End Sub

Shows me 8 weeks (this is wrong).

But the above does not tell me that "November" occurs 5 times, "December" occurs 4 times.

Can I leverage DateDiff to also count the number of times Nov/Dec/Jan etc occurs between start/end dates?

CodePudding user response：

Please, test the next code. It will return the correct number of weeks, October and November months occurrences:

Private Sub testTextEvaluateDate()
 Dim arrD, stDt As Date, enDt As Date, noD As Long, startD As Long, startM As Long, i As Long
 Dim WCount As Long, OctCount As Long, NovCount As Long, prevWNo As Long, wNo As Long
 Const firstWeekDay As Long = vbMonday '(2) you should use here your first day of the week.
                                       ' for Sunday you should use vbSunday, or 1

  stDt = "02-Oct-21": enDt = "27-Nov-21"
  noD = enDt - stDt   1       ' number of involved days between the two date
  startM = month(stDt)        ' month number in stDt
  startD = Day(stDt)          ' day number in stDt
  'create an array of involved dates:
  arrD = Evaluate("TEXT(DATE(2021," & startM & ",row(" & startD & ":" & noD   1 & ")),""dd.mm.yyyy"")")
  
  Debug.Print Join(Application.Transpose(arrD), "|") 'just to see the date range in Immediate Window...
  For i = 1 To UBound(arrD)
    wNo = WorksheetFunction.WeekNum(CDate(arrD(i, 1)), firstWeekDay)
    If wNo <> prevWNo Then prevWNo = wNo: WCount = WCount   1
    If month(arrD(i, 1)) = startM Then OctCount = OctCount   1
    If month(arrD(i, 1)) = month(enDt) Then NovCount = NovCount   1
  Next i
  Debug.Print "Weeks number: " & WCount & vbCrLf & _
              "October month appears " & OctCount & " times." & vbCrLf & _
              "November month appears " & NovCount & " times."
End Sub

The above code builds an array of the necessary days range and analyze it very fast (in memory), extracting what (I understood) you need.

firstWeekDay constant is needed. If "02-Oct-21" would be Sunday on Monday the function to return week number may return a week (ending in Sunday) in plus or in minus. For the range you need (now) it doesn't matter, but if you change the involved date, it may matter, for an accurate return...

If something not clear or other things to be extracted from the days range, please do not hesitate to ask for clarifications.

CodePudding user response：

DateDiff calculates right, as it calculates the difference in week numbers (ww) or month numbers (m) between the two dates.

As you wish to include the start week/month, just add 1:

Public Sub dtConv()

    Dim stDt   As Date
    Dim enDt   As Date
    Dim Weeks  As Integer
    Dim Months As Integer

    stDt = Sheets("New Job Template").Range("F1").Value
    enDt = Sheets("New Job Template").Range("H1").Value

    Weeks = 1   DateDiff("ww", stDt, enDt, vbSunday)
    Months = 1   DateDiff("m", stDt, enDt, vbSunday)

    Debug.Print "Weeks:", Weeks
    Debug.Print "Months:", Months

End Sub