Count the bed filled on a given month/year

[Zee996]

Hello,

I have Admission date and Discharge date. I am looking for a formula that will count the number of bed filled on the given month/year.

Here how the data looks like -

Admission Date	Discharge Date
27-04-2022	Still living on that bed
14-12-2022	13-02-2023
23-01-2023	20-02-2023
17-03-2023	Still living on that bed

Looking for way to find the count by month and year -

	Jan	Feb	Mar	April	May
2023	Count of bed	Count of bed	Count of bed	Count of bed	Count of bed
2022	Count of bed	Count of bed	Count of bed	Count of bed	Count of bed

Would it be helpful if anybody could help with formula.

Thank you.

 

[alansidman]

A Power Query Solution through today. Will update automatically if run daily.

Book1
	A	B	C
2	Admission Date	Discharge Date
3	4/27/2022	Still living on that bed
4	12/14/2022	2/13/2023
5	2/23/2022	2/20/2023
6	3/17/2023	Still living on that bed
7
8	Year	Month Name	Count
9	2022	April	34
10	2022	May	62
11	2022	June	60
12	2022	July	62
13	2022	August	62
14	2022	September	60
15	2022	October	62
16	2022	November	60
17	2022	December	80
18	2023	January	93
19	2023	February	61
20	2023	March	46
21	2023	April	60
22	2023	May	62
23	2023	June	60
24	2023	July	62
25	2023	August	24
26	2022	February	6
27	2022	March	31
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Discharge Date] <> "Still living on that bed" then [Discharge Date] else DateTime.LocalNow()),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type date}, {"Admission Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Discharge Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Discharge"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Dates", each List.Dates([Admission Date], Duration.Days([Discharge] -[Admission Date]) +1 ,#duration(1,0,0,0) )),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Inserted Year" = Table.AddColumn(#"Expanded Dates", "Year", each Date.Year([Dates]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Dates]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Year", "Month Name"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"