You can set up a table which you can then graph.
You can do this with formulas where
- the first column is a list of the dates representing the first of each month
- the second column is a formula returning the counts in that month.
eg
Dates Column:
First Month: I2: =EOMONTH(MIN(Table1[Date Started]),-1)+1
Subsequent Months: =IFERROR(IF(EOMONTH(MAX(Table1[Date Ended]),0)>EDATE(I3,1),EDATE(I3,1),""),"")
Count Column:
=SUMPRODUCT((I3>Table1[Date Started]-DAY(Table1[Date Started]))*(I3<DATE(YEAR(Table1[Date Ended]),MONTH(Table1[Date Ended])+1,0)))
You can also do this using Power Query, which can have some advantages when you update, expand, or contract your data table.
PQ is availabe in Excel 2010 and later. In Excel 2016+, you access this from the Data
tab --> Get&Transform
from Table/Range
after you've selected a cell in the table.
Except for entering the custom column formula, all of the steps can be done from the UI, and pasting the M-Code below into the advanced editor (possibly having to edit the Source Table Name
) will reproduce the UI steps for your examination so as to understand what is being done.
Algorithm
- Add Custom Column which creates a List of the dates, by month, from Start to End
- Extract the values from the list with semi-colon delimiter
- Split the column into rows (using the semicolon delimiter)
- Splitting into rows also effectively unpivots the columns
- Transform the column to the First of the month
- Group the date column with Operation = Count
M-Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Started", type date}, {"Date Ended", type date}, {"Name", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Months", each List.Transform({0..Date.Month([Date Ended])-Date.Month([Date Started])+(Date.Year([Date Ended])-Date.Year([Date Started]))*12}, (x) => Date.AddMonths([Date Started],x))),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Months", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Values", {{"Months", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Months"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Months", type date}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Months", Date.StartOfMonth, type date}}),
#"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Months"}, {{"Count", each Table.RowCount(_), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Months", Order.Ascending}})
in
#"Sorted Rows"