Excel: Generating Months Data from Start and End Date

Question

I would like to use excel to track how many interns I have for each month over the years.

Name	Division	Start Date	End Date
Anna	Div A	12 Jun 2019	1 Jan 2020
Ben	Div B	20 Oct 2020	20 Dec 2020
Clare	Div C	13 Jan 2021	13 Jan 2022

Is there a way that I can use excel to list the number of months each intern work like below so that I am able to put into a graph how many interns I have each month

Month/Year	Name	Div
Jun 2019	Anna	Div A
Jul 2019	Anna	Div A
Aug 2019	Anna	Div A
Sep 2019	Anna	Div A
Oct 2019	Anna	Div A
Nov 2019	Anna	Div A
Dec 2019	Anna	Div A
Jan 2020	Anna	Div A
Oct 2020	Ben	Div B
Nov 2020	Ben	Div B
Dec 2020	Ben	Div B
Jan 2021	Clare	Div C
Feb 2021	Clare	Div C
Mar 2021	Clare	Div C
Apr 2021	Clare	Div C
May 2021	Clare	Div C
Jun 2021	Clare	Div C
Jul 2021	Clare	Div C
Aug 2021	Clare	Div C
Sep 2021	Clare	Div C
Oct 2021	Clare	Div C
Nov 2021	Clare	Div C
Dec 2021	Clare	Div C
Jan 2022	Clare	Div C

Appreciate any help! Thanks in advance :)

Answer 1

I am trying:

Option Explicit
Option Base 1

Private Const NONSENSE As String = "Nonsense"

Public Sub F()
Dim I As Date
Dim S As String
Dim SwitchMonth As Boolean
Dim M As Integer
Dim D1 As Date
Dim D2 As Date

   D1 = "10.10.1990"
   D2 = "01.12.1991"

   If (D1 > D2) Then
      MsgBox ("Failure!")
      Exit Sub
   End If

   M = Month(D1)
   MsgBox (OnMonth(M, Year(D1)))
   SwitchMonth = False
   For I = D1 To D2 Step 1
      
      If Month(I) <> M Then
         M = Month(I)
         MsgBox (OnMonth(M, Year(I)))
      End If
   Next

End Sub

Public Function OnMonth(ByVal M As Integer, ByVal Y As Integer) As String

   If (M < 1) Or (M > 12) Then
      OnMonth = NONSENSE
   End If
   
   Select Case M
      Case 1
         OnMonth = "January"
      Case 2
         OnMonth = "February"
      Case 3
         OnMonth = "March"
      Case 4
         OnMonth = "April"
      Case 5
         OnMonth = "May"
      Case 6
         OnMonth = "June"
      Case 7
         OnMonth = "July"
      Case 8
         OnMonth = "August"
      Case 9
         OnMonth = "September"
      Case 10
         OnMonth = "October"
      Case 11
         OnMonth = "November"
      Case 12
         OnMonth = "December"
   End Select
   
   OnMonth = OnMonth & " " & Y

End Function

Answer 2

This code assumes that the original data is on Sheet1 starting in A1 and puts the result in columns H:J.

All that can be adjusted if needed.

Sub ExpandThings()
Dim arrDataIn As Variant
Dim arrDataOut As Variant
Dim idxRow As Long
Dim cnt As Long
Dim idxMonth As Long

    With Sheets("Sheet1").Range("A1").CurrentRegion
        arrDataIn = .Offset(1).Resize(.Rows.Count - 1)
        ReDim arrDataOut(1 To 3, 1 To Application.Max(.Columns(4)) - Application.Min(.Columns(3)))
    End With

    For idxRow = LBound(arrDataIn, 1) To UBound(arrDataIn, 1)
        For idxMonth = 0 To DateDiff("m", arrDataIn(idxRow, 3), arrDataIn(idxRow, 4))
            cnt = cnt + 1
            arrDataOut(1, cnt) = Format(DateAdd("m", idxMonth, arrDataIn(idxRow, 3)), "mmm yyyy")
            arrDataOut(2, cnt) = arrDataIn(idxRow, 1)
            arrDataOut(3, cnt) = arrDataIn(idxRow, 2)
        Next idxMonth
    Next idxRow
    
    If cnt > 0 Then
        ReDim Preserve arrDataOut(1 To 3, 1 To cnt)
        Range("H1").Resize(, 3).Value = Array("Month/Name", "Name", "Div")
        Range("H2").Resize(cnt, 3).Value = Application.Transpose(arrDataOut)
        
    End If
    
End Sub

Answer 3

You can also obtain your pictured output using Power Query, available in Windows Excel 2010+ and Office 365 Excel

• Select some cell in your original table
• Data => Get&Transform => From Table/Range
• When the PQ UI opens, navigate to Home => Advanced Editor
• Make note of the Table Name in Line 2 of the code.
• Replace the existing code with the M-Code below
• Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

Also note the #"Extracted Month Name" step. This creates the text string that you see in the results table. If you want this to be a "real date", delete this last step from the Applied Steps window, and format that column on the Excel Worksheet for the appearance you desire

M Code

let

//Read in data
//change table name in next line to reflect actual table name in workbook
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Name", type text}, {"Division", type text}, {"Start Date", type date}, {"End Date", type date}}),

//create list of dates by month with intervening months (between start and end) = first of the months
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month/Year", each 
        let 
          StartDate = #date(Date.Year([Start Date]),Date.Month([Start Date]),1),
          EndDate = [End Date],
          mnthList = List.Generate(
                        ()=>StartDate, 
                        each _ <= EndDate, 
                        each Date.AddMonths(_, 1)),

//Replace first and last months in the list with the actual date
          replLast = List.ReplaceRange(mnthList,List.Count(mnthList)-1,1,{[End Date]}),
          replFirst = List.ReplaceRange(replLast,0,1,{[Start Date]})
        in
           replFirst),

//Remove unneeded columns and move the Month/Year column to the beginning
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date", "End Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Month/Year", "Name", "Division"}),

//expand the month list into new rows -- one row for each month
    #"Expanded mnthList" = Table.ExpandListColumn(#"Reordered Columns", "Month/Year"),

//Extract the month name and year as a string for display
//Can omit these steps if you want actual months in the cells
//    in which case you would format them on the worksheet
    #"Extracted Month Name" = Table.TransformColumns(#"Expanded mnthList", {
      {"Month/Year", each Date.MonthName(_) & " " & Text.From(Date.Year(_)), type text}})
in
    #"Extracted Month Name"