It's Sunday here, the last day of the week, and the crazy date system where the week starts on Sunday is again showing it's glorius power. 😅
I need to check that my thoughts are right that this simple trick will solve the Sunday problem with EOWEEK:
EOWEEK(Today()-1)
Or are there caveats to this?
Example snippet:
IN(MONTH(EOWEEK(Today()-1)-5), [monthValue])
(Month(last monday this week), is equal to a value in list monthValue)
Solved! Go to Solution.
Hi @Ratatosk The Monday Slice is an impressive functionality implementation. Great.
If understanding of your requirement is correct, your idea of using EOWEEK(Today()-1) looks good.
Please test well though.
Are you trying to find the last Monday of a month?
I just need to be confident that if I use EOWEEK(Today()-1) It will sort out the problem with filtering in a slice, when sunday comes, because I use ISOWEEKNUM on the rest of the code.
EOWEEK(Today()) works fine on every today() but sundays. Because it then thinks that it should represent the next week. So I had the thought that if EOWEEK believes that today is yesterday I may overcome this problem.
The full code for the MondaySlice is this:
The code can filter by week values, week intervals, or months + odd/even + first week of month, second week of month, etc. last week of month without relying on an underlying calendar.
[vToday] is a virtual today date
OR(
AND( [dateFiltration]="Weeks",
IN(ISOWEEKNUM([vToday]),[weekValue]),
IN(2,[dayValue])
),
AND( [dateFiltration]="Interval",
IN(ISOWEEKNUM([vToday]),[vEveryNWeekResult]),
IN(2,[dayValue])
),
AND( [dateFiltration]="Months",
IN(2,[dayValue]),
IN(MONTH(EOWEEK([vToday]-1)-5),[monthValue]),
OR( AND(ISBLANK([oddEven]), ISBLANK([nWeekOfMonth])),
AND(
MOD(ISOWEEKNUM([vToday]),2)=1,
[oddEven]="Odd"
),
AND(
MOD(ISOWEEKNUM([vToday]),2)=0,
[oddEven]="Even"
),
AND( IN("First", [nWeekOfMonth]),
DAY(EOWEEK([vToday]-1)-5)<=7
),
AND( IN("Second", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday]-1)-5)<=14,DAY(EOWEEK([vToday]-1)-5)>=8)
),
AND( IN("Third", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday]-1)-5)<=21,DAY(EOWEEK([vToday]-1)-5)>=15)
),
AND( IN("Fourth", [nWeekOfMonth]),
AND(DAY(EOWEEK([vToday]-1)-5)<=28,DAY(EOWEEK([vToday]-1)-5)>=22)
),
AND(
IN("Last", [nWeekOfMonth]),
IN(2, [dayValue]),
DATE(EOWEEK([vToday]-1)-5)=
EOMONTH(DATE(EOWEEK([vToday]-1)-5),0)+1-WEEKDAY(EOMONTH(DATE(EOWEEK([vToday]-1)-5),0)+1-2)
)
)
)
)
Hi @Ratatosk The Monday Slice is an impressive functionality implementation. Great.
If understanding of your requirement is correct, your idea of using EOWEEK(Today()-1) looks good.
Please test well though.
Thank you! @Suvrutt_Gurjar .
I've really pushed myself to the limit by doing this "milestone".
Testing will ensue!
User | Count |
---|---|
41 | |
25 | |
25 | |
17 | |
12 |