Count days per month per year between 2 dates

Hello. Hi!

The problem is simple, but the answer doesn't seem so...

I'm trying to count the number of days per month per year between two dates.

So for the following 2 dates:

2013.12.15
2014.02.18
I would have something like:

Year | Month | Days

2013 12 16

2014 01 31

2014 02 18

So, with 2 dates, i would get as many rows as months between then.

I would like to achieve this using only a Select statement, but am completelu lost.

Anyone one out there can crack this? 😀

post representative excel example file with source data and expected result

Originally Posted by bebo021999

Assume A1= start date, A2 = end date
D2:F2 is year, month, days count all the way down
D2:

=IF(EOMONTH($A$1,ROWS($1:1)-1)<=EOMONTH($A$2,0),YEAR(MIN($A$2,EOMONTH($A$1,ROWS($1:1)-1))),"")

E2:

=IF(D2="","",MONTH(MIN($A$2,EOMONTH($A$1,ROWS($1:1)-1))))

F2:

=IF(D2="","",MEDIAN($A$2,DATE(D2,E2+1,0),$A$1)-MEDIAN($A$1,DATE(D2,E2,0),$A$2))

Drag D2:F2 down as far as needed

Attachment 816802

thank you so much for your suggestion

something like this?

From	To		Year	Month Name	Count Days
15/12/2013	18/02/2014		2013	December	17
			2014	January	31
			2014	February	18

Originally Posted by sandy666

something like this?

From	To		Year	Month Name	Count Days
15/12/2013	18/02/2014		2013	December	17
			2014	January	31
			2014	February	18

alsothank you for your sugggestion

Originally Posted by Glenn Kennedy

Hi.

Is your forum profile showing the Excel PRODUCT that you need this request to work with? I have no idea what 0.8 means!!

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

thank you so much for your suggestion

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each {Number.From([start])..Number.From([end])})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"start", "end", "Custom"}, {"Custom.start", "Custom.end", "Custom.Custom"}),
    #"Expanded Custom.Custom" = Table.ExpandListColumn(#"Expanded Custom", "Custom.Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.Custom",{{"Custom.Custom", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Custom.Custom"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "Custom.Custom", "Custom.Custom - Copy"),
    #"Extracted Year" = Table.TransformColumns(#"Duplicated Column",{{"Custom.Custom", Date.Year, Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Extracted Year", {{"Custom.Custom - Copy", each Date.MonthName(_), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Month Name",{"Custom.Custom - Copy"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.Custom"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"