Slice for a specific date to another specific date

[camellia26]

I need to filter daily reports for monthly-report-view.

15th in every month is the due date for its month, like
ex) Monthly-report for 01/2023 -> From 16/12/2022 to 15/01/2023 is the period.

After every month's due date, reports of the previous period should be filtered out, like
ex) After 16/01/2023, reports from the date to next due date(15/02/2023) are shown, though there is no future records of course.

I tried to use EOMONTH() but cannot set specific date with day() because the number of dates are different in each month.
How can I realize this filtering?

[dbaum]

Here's an untested draft to test and modify. I'm not proficient with all the many date-related functions and, so, there may be a simpler approach. Also, be sure to test whether the expression may need to use "Month/Day/Year" syntax despite your locale that uses "Day/Month/Year".

[Date] >= 
CONCATENATE(
    "16", 
    "/", 
    IF(
        DAY(TODAY()) >= 16, 
        CONCATENATE(
            MONTH(TODAY()), 
            "/", 
            YEAR(TODAY())
        ), 
        CONCATENATE(
            MONTH(EOMONTH(TODAY(), -1)), 
            "/", 
            YEAR(EOMONTH(TODAY(), -1)), 
        )
    )
)

[camellia26 · Accepted solution]

>dbaum
Thank you so much. I'm in Japan and "YYYY/MM/DD" format is typical here, so I changed my condition a bit. Your answer is very helpful for me. I added DATE() function to convert the text concatenated to date format.

AND(
  USEREMAIL() = [user].[email],
  [date] >= DATE(CONCATENATE(
    IF(
      DAY(TODAY()) >= 16,
        CONCATENATE(YEAR(TODAY()), "/", MONTH(TODAY())),
        CONCATENATE(YEAR(EOMONTH(TODAY(), -1)), "/", MONTH(EOMONTH(TODAY(), -1)))
      ),"/","16"
    )
  )
)

 USEREMAIL() is just for access control.