Home

Display information by weeks & months on Pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-363675%22%20slang%3D%22en-US%22%3EDisplay%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363675%22%20slang%3D%22en-US%22%3EHi%2C%20last%20week%20I%20was%20trying%20to%20make%20a%20report%20that%20shows%20the%20sales%20evolution%20by%20weeks%20and%20months%20at%20the%20same%20time%20(example%3A%20January%3A%20week%201%2C%20week%202%2C%20week3%2C%20week%204%20-and%20the%20same%20for%20other%20months-).%20When%20researching%20I%20read%20that%20excel%20is%20not%20able%20to%20show%20the%20information%20that%20way.%20Is%20that%20true%3F%20Does%20somebody%20knows%20how%20to%20do%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363675%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363691%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363691%22%20slang%3D%22en-US%22%3ESo%20that%20EXCEL%20can%20understand%20the%20period%20within%20which%20you%20want%20to%20return%20the%20Total%20Sales%2C%20you%20must%20specify%20the%20start%20date%20and%20end%20date%20of%20the%20week%20you%20are%20referring%20to.%20For%20example%2C%20if%20your%20Dates%20are%20in%20A2%3AA100%2C%20your%20Sales%20are%20in%20B2%3AB100%2C%20and%20you%20want%20to%20return%20the%20Total%20Sales%20for%20week%201%20of%20January%202019%2C%20you%20must%20specify%20the%20start%20date%20(1%2F1%2F2019%20in%20F2)%20and%20the%20end%20date%20(1%2F7%2F2019%20in%20G2)%2C%20so%20that%20your%20formula%20should%20look%20like%20this%3A%3CBR%20%2F%3E%3DSUMIFS(B2%3AB100%2CA2%3AA100%2C%22%26gt%3B%3D%22%26amp%3BF2%2CA2%3AA100%2C%22%26lt%3B%3D%22%26amp%3BG2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363688%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20add%20helper%20column%20first%20to%20your%20data%20which%20calculates%20weeknumber%20within%20the%20month.%20Let%20say%20firtst%20week%20of%20the%20month%20starts%20from%20first%20Monday%20and%20triggers%20to%201%20on%20the%20first%20Monday%20of%20next%20month.%20One%20of%20techniques%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Freset-week-number-every-month-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xelplus.com%2Freset-week-number-every-month-excel%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreating%20the%20Pivot%20Table%20you%20add%20both%20Date%20and%20Week%20into%20rows%20pane%20and%20group%20Dates%20by%20months.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carlos_Gomez
Occasional Contributor
Hi, last week I was trying to make a report that shows the sales evolution by weeks and months at the same time (example: January: week 1, week 2, week3, week 4 -and the same for other months-). When researching I read that excel is not able to show the information that way. Is that true? Does somebody knows how to do this?
2 Replies

Hi,

 

You need to add helper column first to your data which calculates weeknumber within the month. Let say firtst week of the month starts from first Monday and triggers to 1 on the first Monday of next month. One of techniques is here https://www.xelplus.com/reset-week-number-every-month-excel/

 

Creating the Pivot Table you add both Date and Week into rows pane and group Dates by months.

So that EXCEL can understand the period within which you want to return the Total Sales, you must specify the start date and end date of the week you are referring to. For example, if your Dates are in A2:A100, your Sales are in B2:B100, and you want to return the Total Sales for week 1 of January 2019, you must specify the start date (1/1/2019 in F2) and the end date (1/7/2019 in G2), so that your formula should look like this:
=SUMIFS(B2:B100,A2:A100,">="&F2,A2:A100,"<="&G2)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies