Anyone try to adjust autocat to check for the day of the week, month or other date searches?
Wondering if I can assign things charged consistently on the 24th to have a seperate description from ones charges on the 7th for the same org.
Thanks in advance!
Wow, that’s pretty specific, doubting it’s possible in the existing structure. In a similar situation, my car insurance and auto insurance show up with the same description, but their amounts are very different. so perhaps you could differentiate that way? I created “Amount Min” and “Amount Max” columns, and set the highest value I could imagine for our auto insurance, and the lowest value I could imagine fore our home insurance. When combined with “Full Description Contains”, each rule is then able to differentiate between the two payments, and apply the “Description” and “Category” appropriate to each.
Hi @wcihahn - I did a fair amount of experimenting with the Advanced Rule Builder not too long ago and it doesn’t seem like your use case would work with existing date fields because those are recognized by the builder as complete dates and the Advanced Rule Builder presents options of Equals, Max, and Min. These would be comparisons against a complete date.
However, I had a similar situation where my wife and I both had credit cards with the same org and I wanted Autocat to be able to tell the payment apart based on the fact one was at the beginning of the month and one was at the end.
What I did was to add a new column defining the Day of the month that each transaction occured.
For example:
={"Day";ARRAYFORMULA(DAY(B2:B))}
In my case, one of the payments was always a single digit day while the other was always two digits, so I used the REGEX term on that Day column to tell them apart.
[0-9]{2}
But since you know the exact day, you could also use the Advanced Rule Builder on that new column with an Equals or Contains criteria for the specific day that you expect. Along with the Description contains, you could affect each transaction from the same org differently.
The regex just gives a little leeway in case it doesn’t hit on the exact day.
All that said, I haven’t seen it run recently because I stopped using that card months ago. I do believe it was working. The only hang-up I can imagine might be because those are string operations on a number. However my recollection is that the AutoCat script handles that just fine.
If not, you could force it to a text like the formula below. Good luck!
={"Day";ARRAYFORMULA(TEXT(DAY(C2:C),"#"))}
Along a similar lines to @KyleT , add a Day column to the Transactions sheet using this formula:
=LET(tDates, INDIRECT(LET(ltr,CHAR(64+XMATCH("Date",INDIRECT("A1:Z1"))),ltr&"2:"<r)),
{"Day";ARRAYFORMULA(IF(tDates<>"",DAY(tDates),""))}
)
It accomplishes the same thing with creating a Day value for each transaction, but the formula range won’t change from B2 to B3, etc, when a Manual Transaction or other row insertion above row 2 occurs and will be blank if/when the Date is blank for some reason, instead of a false Day value.
Then, add Day Min and Day Max columns to the AutoCat sheet for specifying the Day range you want.
I love this solution and tried adding this but I am getting an #REF error: “Result was not automatically expanded, please insert more rows (1).”
Any idea why, @Mark.S? Appreciate your help!
Adam
Put the formula in row 1 (not row 2). The formula includes naming the column.
Fantastic, thank you!
BTW, you may have introduced a bunch of blank rows at the bottom the sheet when you did that, just delete them.