Slice based on date range

AppSheet AppSheet Q&A
,
1

Hello all!
I have a table with a Date/Time collumn [Date] and I’m trying to create a slice to show only the rows from the previous month. For example, when viewing the app in April (from April 1 to April 30) the slice will show only the rows with[Date] values between March 1 and March 31 of the current year.

Could anyone point me in the righr direction to create this slice, please?

2

MONTH([date_time]) = (MONTH(TODAY()) - 1)

matches records that have a date time value belonging to last month.

1 Like
3

To add to @TeeSee1 , you will need to add a YEAR() condition to have this work with data that covers multiple years.

AND(MONTH([Date]) = (MONTH(TODAY()) - 1), YEAR([Date])=(YEAR(TODAY())))

2 Likes
4

Now that you pointed that out, I also realized that the case where it expands across two years needs to be considered..

IF(
 MONTH(TODAY()) = 1,
 AND(MONTH([Date]) = 12, YEAR([Date])=(YEAR(TODAY()) - 1))
 AND(MONTH([Date]) = (MONTH(TODAY()) - 1), YEAR([Date])=(YEAR(TODAY()))
)

Edited, there should be a comma at the end of line 3.

2 Likes
5 
IF(
 MONTH(TODAY()) = 1,
 AND(MONTH([Date]) = 12, YEAR([Date])=(YEAR(TODAY()) - 1))
 AND(MONTH([Date]) = (MONTH(TODAY()) - 1), YEAR([Date])=(YEAR(TODAY()))
)

Thank you @TeeSee1 and @lynchk21 for your help!
I’ve tryed the expression above, but I’m getting this error:

IF function is used incorrectly:three inputs should be provided — (logical-expression, value-if-true, value-if-false).

6

IF(

MONTH(TODAY())=1,

AND(12=(MONTH([Date])),Year(TODAY())-1=YEAR([Date])),

AND((MONTH(TODAY()))-1=(MONTH([Date])),Year(TODAY())=YEAR([Date]))

)

1 Like
7

I have this expression in one of my apps and it works, let me know if you are still getting an error

8

Thank you @lynchk21
It’s working fine!