Automating a report; splitting the rich text checked boxes by time checked.

[LindsayRidpath]

Hello, 

I'm not sure which category this would fall under. I'm working on generating an automated report based on our data. I don't want it to be too long, so under one filtered category, I only want the report to pull information tied to a checked-off box that was only checked off in the last 7-14 days. (I'd like to be able to change the date as needed) The checkboxes live in our subtasks field and are rich text. 

Is there a way to sort when these boxes where checked based on date? For example, in the screenshot below, I only want to pull the checked boxes with green, not the ones with red: 

Is this possible? Thanks a bunch! 

Thank you,

Lindsay 

[Sho · Accepted solution]

good!

To get to two weeks, just extend the formula longer and longer.

IF(
  REGEX_MATCH(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  ),
  REGEX_EXTRACT(
    {Subtasks},
    "(^(.*\n)*.* \\((" &
    DATETIME_FORMAT(TODAY(), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -1, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -2, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -3, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -4, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -6, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -7, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -8, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -9, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -10, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -11, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -12, "days"), "YYYY/MM/DD") & "|" &
    DATETIME_FORMAT(DATEADD(TODAY(), -13, "days"), "YYYY/MM/DD") &
    ")\\)" & ")"
  )
)