Disregard year when filtering dates in slice

Ratatosk
Participant V

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 Solved
0 10 290
1 ACCEPTED 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")))
)
)

View solution in original post

10 REPLIES 10

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.

Ratatosk
Participant V

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") ))


)
)
Top Labels in this Space