I realize now it may be a terrible way to create a log, but I currently have a column this is just an ongoing text list of dates and times that looks like this:
8/19/2024 12:36 PM
8/7/2024 4:09 AM
7/31/2024 6:44 AM
7/30/2024 12:46 PM
I want to be able to delete all entries for a specific month, say July. Or at least I want to delete all the dates and understand it will be a lot harder to delete the times.
I discovered that I could get rid of one date with thisRow.[Column1].Substitute(“7/31/2024”, “”).
I hoped I could then get rid of all July dates by doing this…
thisRow.[Column1].Substitute(“7/31/2024”, “”) AND thisRow.[Column1].Substitute(“7/30/2024”, “”) AND thisRow.[Column1].Substitute(“7/29/2024”, “”) ETC.
…but this didn’t work with “AND” or “+” between commands.
Is there some other way I might be able to do this?
Many thanks, Gregg
Hi Gregg, although you can use substitute in substitute formula, I would prefer month() to find specific months if I were you. If you want to find month = July, just use month. If you want to remove date but keep time, you ca use something like below:
if(Month(thisRow.Column1)=7,Time(Hour(thisRow.Column1),Minute(thisRow.Column1),Second(thisRow.Column1)),thisRow.Column1)
Hi, Gun…thank you for this!
I probably didn’t make it clear, but there are multiple lines of dates and times in a single cell like this:
Is there a way I can use a formula to remove all the lines with July data, or just the July dates and leave the times if necessary?
Many thanks again!
Gregg
Hey @Gregg_Stebben - There’s actually a pretty simple formula that will do what you’re looking for:
- Split the lines by the line break, which will turn the text of dates into a Coda List
- Use the filter function to strip out months you don’t want by converting each value to a Datetime
- Join the filtered list by a line break
This is what it looks like
And you can see it strips out all July dates
you can of course get more complex with the filter function if needed. You could also convert all the values to dates so when you join the list back to together it corrects any inconsistencies in the date formatting (ex: if there’s a mix of “AM”, “am” everything looks like “AM” after).
To make the lines easier to read, you can use Linebreak() instead of Character(10)
Cheers, christiaan
Fantastic, thank you so much!
I learned a lot from your help and ending up tweaking it like this to get the perfect result:
This was a huge help…thank you!
Hmmm… when I see this I wonder, is that really a good way to store transactional data? Perhaps you inherited this, but there’s a really good way to avoid this data storage approach that pays some real dividends.
It’s not transactional data, it is literally a list of auto-generated dates that meet a criteria…if that matters.
It is definitely my own hacked-together system…that seems to work ok until I need to extract data.
I am VERY open to suggestions on how to improve this.
I actually copied this from another one of my own tables where I I hit a button to add the current date and time to the top of the current cell and then key in commentary. Again, not a bad system until I want to extract data.
For the column with both current date and time and commentary, I am now trying to figure out how to remove all the commentary and just have the dates.
Your note makes me think there’s a better way to do all of this…but at the moment my priority is figuring out how to just extract the dates and remove the commentary.
I am attaching a screenshot here, any thoughts on both the current task of extracting just dates and also a better way to do it as you suggest?
Many thanks for any help you can offer!
And there’s the rub. 
Why not embed a table in that cell such that you eliminate formatting errors? Canvases can have subtables and this list of dates could be treated like data instead of one big string.
You could also use a relational subtable where all these dates exist for all rows wherein, the row relationship allows you to view only the dates associated with a given row.
Hi, Bill…I hope you don’t mind me asking a follow-up question or two.
The subtable idea is great…I didn’t understand that was possible.
My one question/concern:
If I say, add 1000 entries a year to this subtable, is that going to slow down the entire document?
Unlikely. These dates are small and even if it were 10x that, I wouldn’t anticipate an issue. The key is to store them as data instead of strings. Structure values are so much easier to deal with when that question comes up to list them, sort them, search them.
We use integrated relational tables all the time and some are pretty large (north of 20k rows) without issue.
Another approach is to store them in a text field as JSON data which can also be easily enumerated with CFL. It’s less readable, but you can also add another field for human viewing pleasure - i.e., a formula that transforms the JSON items into a formatted list.
Agreed with @Bill_French2 re: subtables and/or JSON.
at the moment my priority is figuring out how to just extract the dates and remove the commentary.
So for the moment, for the extracting–maybe try regex? The below embed may do it for you, but in case it doesn’t, I’ve also included some resources.
RESOURCES
For Learning:
- RegexOne - Learn Regular Expressions - Lesson 1: An Introduction, and the ABCs
- (Disclaimer: I didn’t go through the whole tutorial here, but IIRC have used it for occasional concepts I’ve had trouble grasping)
- Regular Expression Tutorial - Learn How to Use Regular Expressions (regular-expressions.info)
- This one gets into more complex concepts, but fair warning: it makes my brain hurt sometimes, haha. But then I’ve never properly learned Regex…I just sort of…play till I find the right one
- This one gets into more complex concepts, but fair warning: it makes my brain hurt sometimes, haha. But then I’ve never properly learned Regex…I just sort of…play till I find the right one
For Testing:
- I believe Coda uses a system most similar to the JavaScript flavor (? someone correct if I’m wrong), so make sure that’s selected when using either of these
Coda-Specific:
- Coda-specific testing: Coda Regex Formulas Tool - Showcase / Tips and Hacks - Coda Maker Community
- @Eric_Koleda’s Pack that I use on a semi-regular basis: Regex+ Pack, extend Coda with Regex+ - Coda
FYI @Amy_Weatherford - Coda recently added support for a native RegexExtract formula, so you likely don’t need to use the Pack anymore:
Thanks! I need to pay better attention to updates, I guess… I wonder if there’s a way to pull that data into a Coda doc…
I did create a Formulas Metadata Pack you can use to sync in all of the formulas, and then you could create some automations that alert you to new ones.
Oooh good idea, thanks! I think I already have the formulas in a doc from your Pack, I just hadn’t thought about automations… I’ll have to do that!