Hi Paul,

No file atatched.

regards

Phil

This time 🙂

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Students", Int64.Type}, {"Year Finshed", type text}, {"Start Date", Int64.Type}, {"End Date", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {[Start Date]..[End Date]}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Start Date", type date}, {"End Date", type date}, {"Dates", type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Dates]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Dates]), Int64.Type),
#"Sorted Rows" = Table.Sort(#"Inserted Month",{{"Month", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Month"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Month Name"]), "Month Name", "Dates", List.Count)
in
#"Pivoted Column"

Not sure if Alan's Power Query solution gives you the results asked for. Please find a different (formula based) solution in the attached file.

Thanks Gents,

Riny; the shortened formula would be easier use but I think my limitation is how the data is down loaded I have attached a sample).  As you will see; I have attempted to use a SUMIFs but it is not, it appears, to be returning the correct information.

I am faced with competing issues; as a business we are still (until late next year) stuck with MS 2013 and secondly they want the info draw down from SharePoint (again 2013); so there are competing struggles with the ability to draw down and calc or create the calc fields in SharePoint. 

Anyway any guidance will be greatly appreciated.

Regards Paul

Your initial sample suggests you don't care about which year it is, just the month name - is that correct?

I think it's probably simplest just to generate a list of entries for each month from start date to end date, then split into rows before aggregating by month - either in PQ or with a pivot table.

Thanks Velouria,

I like the concept; but possiblt too much work as the process will be requireing ongoing updating; and I am trying to sort data from 2016 to today (attempting to gather and having ongoing logitudinal volume for optimising training periods and reducing strerss volumes).

I have attached another sample using Riny's formula (created the calc colums in Sharepoint to replicate what Riny supplied); however the formula doesn't appera to wor, not sure if it's because I have done something wrong in the array or if it's just because I, using an older version of excel.

Anyway any guidance greatly appreciated

Paul 

See attached. It didn't work because the yyyy-mmm entries in columns D and E are texts, whereas the dates in row 2 are real (numerical dates).

Went back to my original file and changed yours to work accordingly, using the real start and end dates in stead of the yyyy-mmm columns. Then I also changed row 2 to contains month-END dates.

Entered the formula with C-S-E since that how your file had it and copied the lot down. Seems to work just fine.

I'm not sure how it would be too much work - the query does it all? See attached demo - I used a pivot table for the output but you could also make it a table if you wanted to.

Riny & Velouria,

I thanks you both very much for your assistance, it's likely both your options will be in use when I develop my final plan.  I'm really annoyed that I missed the text vs number format, doh.

Anyway thanks again Gents

Live Long and Excel