The below code works for filtering a slice, but Iโd like to disregard the year in the formula so it can be set up as a recurring event each year.
Am I going about this all wrong? Any ideas on what I can do or if it is even possible to do that with dates?
AND([Tjeneste]=โVaktmesterโ, or(and(Isblank([PauseToDate]), isblank([PauseFromDate])),and(Date([PauseToDate])<Date(Today()),Date([PauseFromDate])>Date(Today()))))
Solved! Go to Solution.
I think I got it now. Tests work out so far.
Now I can hide rows that are within a range of dates with slices. (So they only show when today() is not within that range)
AND([Tjeneste]="Vaktmester", OR(
AND(ISBLANK([PauseToDate]), ISBLANK([PauseFromDate])),
AND(NUMBER(TEXT([PauseToDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD"))),
AND(NUMBER(TEXT([PauseToDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")))
)
)
Will the table contain multiple year dates in the columns [PauseToDate] and [PauseFromDate] ?
Yes.
Thank you. In that case your expression seems to be Okay. Any specific issue you are facing? And any specific reason you have again applied DATE() to all those date columns and TODAY()
Itโs not ok, because I want to filter the date between lets say 15.03-20.08 both in year 2021and 2022 Even though it is listed as only 2021.
fre. 26. mar. 2021, 12:51 skrev Suvrutt Gurjar via AppSheet Creator Community <appsheet@discoursemail.com>:
Maybe the following , if I have understood your requirement correctly
AND([Tjeneste]=โVaktmesterโ, OR(AND(ISBLANK([PauseToDate]), ISBLANK([PauseFromDate])), AND(NUMBER(TEXT([PauseToDate], โMMDDโ))<NUMBER(TEXT(TODAY(), โMMDDโ)),NUMBER(TEXT([PauseFromDate], โMMDDโ))>NUMBER(TEXT(TODAY(), โMMDDโ) ))))
I have no idea how this worked, but it did! Thank you so much Suvrutt_Gurjar!
EDIT: After some more tests it didnโt work. Is it because I have DDMM as my locale setting?
And sorting that wouldnโt work when dd is first?
EDIT2: I think there is something wrong with my logic here. Will post when I figure out
I think I got it now. Tests work out so far.
Now I can hide rows that are within a range of dates with slices. (So they only show when today() is not within that range)
AND([Tjeneste]="Vaktmester", OR(
AND(ISBLANK([PauseToDate]), ISBLANK([PauseFromDate])),
AND(NUMBER(TEXT([PauseToDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD"))),
AND(NUMBER(TEXT([PauseToDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")))
)
)
Great, thank you for the update, and nice to know it works. Hope it is working exactly as you require.
Thanks again for leading me the right way.
As Iโve recently discovered I am a moron. Hereโs the new new version. I still canโt totally grasp it wholly with my peanut brain, but this works in all conditions tested so far.
AND([Tjeneste]="Vaktmester", OR(
AND(ISBLANK([PauseToDate]), ISBLANK([PauseFromDate])),
AND(
NUMBER(TEXT([PauseFromDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseToDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))>NUMBER(TEXT([PauseToDate], "MMDD") ))
AND(
NUMBER(TEXT([PauseFromDate], "MMDD"))<NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseToDate], "MMDD"))>NUMBER(TEXT(TODAY(), "MMDD")),
NUMBER(TEXT([PauseFromDate], "MMDD"))<NUMBER(TEXT([PauseToDate], "MMDD") ))
)
)
User | Count |
---|---|
43 | |
29 | |
25 | |
23 | |
13 |