Week of the Month Popularity

Walker_Ice

Board Regular
Joined
Oct 6, 2023
Messages
50
Office Version
  1. 2021
Platform
  1. MacOS
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) ''")
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
Below is a sample set of the data I am working on.

TEsting_Excel_File.xlsx
ABCDE
1Income Services
2ServiceDatePaymentTipsTotal
3MDL4-Feb-21$900.00$0.00$900.00
4LDL13-Mar-23$300.00$25.00$325.00
5POL17-Jun-22$200.00$25.00$225.00
6POW7-Jun-23$900.00$0.00$900.00
7MAN10-Jul-23$1,900.00$0.00$1,900.00
8INO31-Mar-24$23.00$10.00$33.00
9YOU26-May-23$875.00$10.00$885.00
10TBW18-Feb-23$750.00$10.00$760.00
11DPW1-Jun-23$600.00$10.00$610.00
12MDL18-Feb-24$600.00$5.00$605.00
13RDL18-Mar-23$850.00$5.00$855.00
14NAM28-May-22$850.00$0.00$850.00
15PPP8-Feb-23$10,000.00$10.00$10,010.00
16PPE3-Mar-23$100.00$10.00$110.00
17PPE4-Jan-21$100.00$0.00$100.00
18INO14-Feb-23$23.00$0.00$23.00
19LDL1-Apr-23$300.00$5.00$305.00
20HDL3-Jan-22$8,500.00$0.00$8,500.00
21AND25-Feb-23$20,000.00$0.00$20,000.00
22WWW26-Jun-25$700.00$5.00$705.00
23WDL10-Mar-23$899.00$5.00$904.00
Sheet5
Cell Formulas
RangeFormula
E3:E23E3=SUM(C3:D3)
 
Upvote 0
to get the week of the month, use power query
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
GHIJKL
2ServiceDatePaymentTipsTotalWeek of Month
3MDL2/4/202190009001
4LDL3/13/2023300253253
5POL6/17/2022200252253
6POW6/7/202390009002
7MAN7/10/20231900019003
8INO3/31/20242310336
9YOU5/26/2023875108854
10TBW2/18/2023750107603
11DPW6/1/2023600106101
12MDL2/18/202460056054
13RDL3/18/202385058553
14NAM5/28/202285008504
15PPP2/8/20231000010100102
16PPE3/3/2023100101101
17PPE1/4/202110001002
18INO2/14/2023230233
19LDL4/1/202330053051
20HDL1/3/20228500085002
21AND2/25/2023200000200004
22WWW6/26/202570057054
23WDL3/10/202389959042
Sheet1
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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),
    #"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?
 
Upvote 0
That is perfect! Thank you for your support. I appreciate your help in solving this.
 
Upvote 0
Is there a way that the results can say "1st week", "2nd week", 3rd week", 4th week", "5th week", and "6th week"?
 
Upvote 0
Add a column
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", 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"
 
Upvote 0
Solution

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top