Week of the Month Popularity

[Walker_Ice]

Hi Everyone,

I have this formula (See Below) to tell me which days of the week is most popular.

But now I am trying to figure out how can I go into my date column "Income_Dates" and find out if the appointments are in the 1st, 2nd, 3rd, 4th, or 5th week of the month. Trying to figure out which week is the most popular for booking appointments. Is it towards the end of the month or the beginning of the month.

Any help would be appreciated.

WeekDay-Formula:

=QUERY({ArrayFormula(TEXT(Income_Dates, "dddd")), Income_Dates}, "SELECT Col1, COUNT(Col2) WHERE Col1 is not null GROUP BY Col1 LABEL COUNT(Col2) ''")

 

[alansidman]

It will be beneficial if you provide a sample which is representative of your actual data. 10-15 records and a mocked up solution. No pictures as we cannot manipulate data in a picture. Use XL2BB to load your data.

 

[Walker_Ice]

Below is a sample set of the data I am working on.

TEsting_Excel_File.xlsx
	A	B	C	D	E
1	Income Services
2	Service	Date	Payment	Tips	Total
3	MDL	4-Feb-21	$900.00	$0.00	$900.00
4	LDL	13-Mar-23	$300.00	$25.00	$325.00
5	POL	17-Jun-22	$200.00	$25.00	$225.00
6	POW	7-Jun-23	$900.00	$0.00	$900.00
7	MAN	10-Jul-23	$1,900.00	$0.00	$1,900.00
8	INO	31-Mar-24	$23.00	$10.00	$33.00
9	YOU	26-May-23	$875.00	$10.00	$885.00
10	TBW	18-Feb-23	$750.00	$10.00	$760.00
11	DPW	1-Jun-23	$600.00	$10.00	$610.00
12	MDL	18-Feb-24	$600.00	$5.00	$605.00
13	RDL	18-Mar-23	$850.00	$5.00	$855.00
14	NAM	28-May-22	$850.00	$0.00	$850.00
15	PPP	8-Feb-23	$10,000.00	$10.00	$10,010.00
16	PPE	3-Mar-23	$100.00	$10.00	$110.00
17	PPE	4-Jan-21	$100.00	$0.00	$100.00
18	INO	14-Feb-23	$23.00	$0.00	$23.00
19	LDL	1-Apr-23	$300.00	$5.00	$305.00
20	HDL	3-Jan-22	$8,500.00	$0.00	$8,500.00
21	AND	25-Feb-23	$20,000.00	$0.00	$20,000.00
22	WWW	26-Jun-25	$700.00	$5.00	$705.00
23	WDL	10-Mar-23	$899.00	$5.00	$904.00
Sheet5

Cell Formulas
Range	Formula
E3:E23	E3	=SUM(C3:D3)

 

[alansidman]

to get the week of the month, use power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type)
in
    #"Inserted Week of Month"

Book3
	G	H	I	J	K	L
2	Service	Date	Payment	Tips	Total	Week of Month
3	MDL	2/4/2021	900	0	900	1
4	LDL	3/13/2023	300	25	325	3
5	POL	6/17/2022	200	25	225	3
6	POW	6/7/2023	900	0	900	2
7	MAN	7/10/2023	1900	0	1900	3
8	INO	3/31/2024	23	10	33	6
9	YOU	5/26/2023	875	10	885	4
10	TBW	2/18/2023	750	10	760	3
11	DPW	6/1/2023	600	10	610	1
12	MDL	2/18/2024	600	5	605	4
13	RDL	3/18/2023	850	5	855	3
14	NAM	5/28/2022	850	0	850	4
15	PPP	2/8/2023	10000	10	10010	2
16	PPE	3/3/2023	100	10	110	1
17	PPE	1/4/2021	100	0	100	2
18	INO	2/14/2023	23	0	23	3
19	LDL	4/1/2023	300	5	305	1
20	HDL	1/3/2022	8500	0	8500	2
21	AND	2/25/2023	20000	0	20000	4
22	WWW	6/26/2025	700	5	705	4
23	WDL	3/10/2023	899	5	904	2
Sheet1

 

[Walker_Ice]

I plugged in this formula but it does not seem to be working.

Also, my goal is to have a small 5 row table, which shows 1st, 2nd, 3rd, 4th, and 5th week of the month. And just have the counts displayed in that 5 row table.

 

[alansidman]

Show us your mocked up results from the sample shown. And that is not what was requested in the original post

find out if the appointments are in the 1st, 2nd, 3rd, 4th, or 5th week of the month. T

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Week of Month", {"Week of Month"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Are you familiar with Power Query?

 

[Walker_Ice]

That is perfect! Thank you for your support. I appreciate your help in solving this.

 

[Walker_Ice]

Is there a way that the results can say "1st week", "2nd week", 3rd week", 4th week", "5th week", and "6th week"?

 

[alansidman]

Add a column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Week of Month", {"Week of Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Week", each if [Week of Month]= 1 then "1st Week"
        else if [Week of Month]= 2 then "2nd Week"
        else if [Week of Month]= 3 then "3rd Week"
        else if [Week of Month]= 4 then "4th Week"
        else if [Week of Month]= 5 then "5th Week"
        else "6th Week"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Week", "Week of Month", "Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Week of Month"})
in
    #"Removed Columns"