Filter condition "Last Week" - considering week from Monday to Sunday

[ErikKontar]

Hi guys,


I have a question I was not able to find an answer anywhere so I need to ask your
advice, hope you can help me
When I use Filter condition "Last week" it always considering the week from Sunday to
next Saturday, is there an option to setup this to consider the week from Monday to
Sunday?
Because I need to extend my macro to be able to use it also on Mondays, as I need
always data from previous day, so in case of Mondays it would be data from Friday +
Saturday + Sunday.
The solution would be really easy with "WEEKDAY" function + "Last Week" filter
condition but this is the only thing what holding me back.
I already tried anything but I think there should be some kind of setting in Excel, could
someone help me to solve this? Would much appreciate your help.


Thanks

 

[WaterGypsy]

Have you checked your Windows settings.... go to Control Panel then Region & Language

 

[ErikKontar]

yes I also checked windows settings but there the week starts with Monday, so I was wondering if there is something separate settings only in excel.

 

[JrayK2]

You could use the weeknum and adjust the return type to 2, then just filter to previous weeknum

=weeknum(a1,2)
[TABLE="class: banded flipColors, width: 768"]
<thead style="box-sizing: border-box;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DADADA]#DADADA[/URL] "]
[TH="align: left"][FONT=&quot][FONT=&quot]Return_type[/FONT][/FONT]
[/TH]
[TH="align: left"][FONT=&quot][FONT=&quot]Week begins on[/FONT][/FONT]
[/TH]
[TH="align: left"][FONT=&quot][FONT=&quot]System[/FONT][/FONT]
[/TH]
[/TR]
</thead><tbody style="box-sizing: border-box;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]1 or omitted[/FONT]
[/TD]
[TD][FONT=&quot]Sunday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]2[/FONT]
[/TD]
[TD][FONT=&quot]Monday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]11[/FONT]
[/TD]
[TD][FONT=&quot]Monday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]12[/FONT]
[/TD]
[TD][FONT=&quot]Tuesday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]13[/FONT]
[/TD]
[TD][FONT=&quot]Wednesday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]14[/FONT]
[/TD]
[TD][FONT=&quot]Thursday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]15[/FONT]
[/TD]
[TD][FONT=&quot]Friday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]16[/FONT]
[/TD]
[TD][FONT=&quot]Saturday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]17[/FONT]
[/TD]
[TD][FONT=&quot]Sunday[/FONT]
[/TD]
[TD][FONT=&quot]1[/FONT]
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]21[/FONT]
[/TD]
[TD][FONT=&quot]Monday[/FONT]
[/TD]
[TD][FONT=&quot]2

[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]
https://support.office.com/en-us/article/WEEKNUM-function-e5c43a03-b4ab-426c-b411-b18c13c75340

 

[ErikKontar]

this would be fine, but how can I filter out only previous weeknum? Is there some option what recognize which is the previous weeknum?
Thanks a lot