SOLVED
Home

Incorrect dates with the last week filter in a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-744373%22%20slang%3D%22en-US%22%3EIncorrect%20dates%20with%20the%20last%20week%20filter%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744373%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20big%20table%20with%20transactions%20from%2001-Jan-2019%20until%20today%20(more%20then%20100%20000%20rows%20so%20all%20dates%20have%20transactions).%20I%20want%20to%20make%20pivot%20tables%20where%20I%20filter%20on%20this%20and%20last%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20don't%20receive%20the%20correct%20dates.%20This%20week%20is%20Monday%20the%208th%20to%20Sunday%20the%2014th%20of%20July%20and%20last%20week%20was%20Monday%20the%201st%20to%20Sunday%20the%207th%20of%20July.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20after%20I%20use%20date%20filters%20are%20last%20week%201st%20to%206th%2C%20only%206%20days%20and%20ends%20on%20a%20Saturday%20and%20this%20week%20is%207th%20to%20today%209th.%20How%20do%20I%20change%20the%20filter%20to%20include%207%20days%20and%20Monday%20to%20Sunday%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20231px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122679i4F825ABA6149AD62%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22week.PNG%22%20title%3D%22week.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744373%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744437%22%20slang%3D%22en-US%22%3ERe%3A%20Incorrect%20dates%20with%20the%20last%20week%20filter%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744437%22%20slang%3D%22en-US%22%3EAre%20you%20sure%20your%20data%20includes%20the%20seven%20days%20of%20last%20week%3F%3CBR%20%2F%3EI%20don't%20think%20you%20can%20change%20what%20Excel%20defines%20as%20last%20week.%20See%20this%20discussion%3A%20%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F25035822%2Fhow-to-set-the-week-start-day-in-a-pivot-table%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F25035822%2Fhow-to-set-the-week-start-day-in-a-pivot-table%3C%2FA%3E%3C%2FLINGO-BODY%3E
Wiiilda
New Contributor

I have a big table with transactions from 01-Jan-2019 until today (more then 100 000 rows so all dates have transactions). I want to make pivot tables where I filter on this and last week.

 

But I don't receive the correct dates. This week is Monday the 8th to Sunday the 14th of July and last week was Monday the 1st to Sunday the 7th of July.

 

The result after I use date filters are last week 1st to 6th, only 6 days and ends on a Saturday and this week is 7th to today 9th. How do I change the filter to include 7 days and Monday to Sunday?

week.PNG

 

 

1 Reply
Solution
Are you sure your data includes the seven days of last week?
I don't think you can change what Excel defines as last week. See this discussion: https://stackoverflow.com/questions/25035822/how-to-set-the-week-start-day-in-a-pivot-table
Related Conversations
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Page properties not visible for filtering news webpart
stevenam in SharePoint on
12 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
How do I Find/Replace or Filter with exact words/characters?
MattC475 in Excel on
5 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies