Oct 31, 2023 02:49 PM
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
Solved! Go to Solution.
Oct 31, 2023 09:19 PM
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") &
")\\)" & ")"
)
)
Oct 31, 2023 05:24 PM
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.
Oct 31, 2023 07:19 PM
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") &
")\\)" & ")"
)
Oct 31, 2023 07:27 PM
Can you provide the format of the rich text dates?
Oct 31, 2023 08:06 PM
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!
Oct 31, 2023 08:26 PM
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") &
")\\)" & ")"
)
)
Oct 31, 2023 08:50 PM
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
Oct 31, 2023 08:57 PM
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🤔
Oct 31, 2023 09:04 PM
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
Oct 31, 2023 09:07 PM
Also, is there a way to expand the dates extract? One week to two weeks?