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 

[stevendasilva]

I would make a field called "checkbox last updated". Use the last updated field and only apply it to the checkbox. 

You can then use the "checkbox last updated" to filter out the specific timeframe you are looking for. 

Filter for: Checkbox checked, date is less than or greater than X. 

See the attachment for an example. 

[Sho]

Hi @LindsayRidpath ,

For example, in this case, if there is a date at the end of the list, filtering is possible.
However, the format needs to be consistent and ordered by date.

REGEX_EXTRACT(
  {Notes},
  "(^(.*\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") &
  ")\\)" & ")"
)

 

[stevendasilva]

@LindsayRidpath

Can you provide the format of the rich text dates? 

[LindsayRidpath]

Hi, @stevendasilva,

Thank you for your earlier suggestion. I'm looking for something that will extract the checked boxes after so many days. So up to this point, I haven't added dates to the rich text, only checked boxes. Is that what you're asking in your later message? 

@Sho , thank you for the formula, this might be more like what I'm looking for. Since I haven't added dates, I retroactively input a few to test the formula. Do you see why I might be getting the error? 

Thank you both for your help and insight! 

[Sho]

Ah, if the date was not found, there was an error.
In case of a workaround, it will look like this.

IF(
  REGEX_MATCH(
    {Notes},
    "(^(.*\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") &
    ")\\)" & ")"
  ),
  REGEX_EXTRACT(
    {Notes},
    "(^(.*\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") &
    ")\\)" & ")"
  )
)

[LindsayRidpath]

Thank you Sho,

What if I put in the date? I just used the formula you provided and it turned back blank. 

I'm not sure what I'm missing. Thank you for your time,

Lindsay 

[Sho]

This formula extracts up to one week before today.　That is, until 10/26.
If you want to include unchecked rows, it needs to be reconsidered🤔

[LindsayRidpath]

Ah! Ok, got it--almost! 🙂 

Sorry for the confusion, so I've updated the dates, and removed the date from the unchecked box. The unchecked box is showing up in the formula field, which I don't want. I only want the checked boxes from the last week or two. Do you know why this is? 

Thanks again,

Lindsay 

[LindsayRidpath]

Also, is there a way to expand the dates extract? One week to two weeks?