topic Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help in DemandTools

Import Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

michael-wtds — Tue, 29 Apr 2025 13:44:42 GMT

@AnthonyValidity I'm trying to match the Close Date on MM-YYYY, and can't get this regex match to match a test record with the exact same date.
(\d{4}-\d{2})-\d{2}

Have you seen any examples of date matching regex that I can try to use?

Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

AnthonyValidity — Mon, 28 Apr 2025 19:07:13 GMT

Hi @michael-wtds ,

You're using Modify to try to match records? Can you give me the full use case? Also, what information is contained in the Close Date field? Does it look like this on the backend, YYYY-MM-DD?

Best,

Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

michael-wtds — Mon, 28 Apr 2025 19:15:08 GMT

I solved this with a transform formula. The issue is my input file date format was m/d/yyyy, but Salesforce dates are yyyy-mm-dd. So, the regex pattern matching wasn't matching because the values were in different orders.

In my scenario, I'm using an import file, and on the field mapping for Close Date I applied the transform formula -
DATE(YEAR(${CloseDate}), MONTH(${CloseDate}), DAY(${CloseDate}))
- And when checking with sample date the date format of 04/28/2025 was shown as 2025-04-28.

Then in the match step for Close Date, this RegEx comparison either of these patterns matched my dates with the same month and year:
(\d{4})[-,\/](\d{1,2})
- matches with a known separator (dash or slash)

(\d{4}).(\d{1,2})
- matches 4 digits then 1 or 2 digits with anything in between. Not as safe.

Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

michael-wtds — Mon, 28 Apr 2025 21:41:55 GMT

Hi @AnthonyValidity,

I'm working on an investment file import into Opportunities using upsert with a matching step. I'm trying to match the Close Date from my file to existing Opportunity records.

In my file, the investment Close Date be 4/4/2025 (m/d/yyyy), while the Opportunity Close Date might be 4/10/2025. I want to find records where the month and year match, ignoring the specific day.

I initially discovered that the regex matching only worked reliably when using four consecutive digits (\d{4}). However, when I tried adding the month pattern (\d{1,2}), either before or after the year, it failed to match records even when the dates were exactly the same.

When I converted my file's dates to match Salesforce’s storage format (yyyy-mm-dd), the regex pattern matching started working correctly.

What was unexpected is that, although my file contained valid date values, the RegEx comparison failed because the file format didn’t match Salesforce’s format. I was expecting DemandTools V to normalize the file date format automatically before performing the regex matching.

I hope this helps!

Thanks,
Michael

Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

AnthonyValidity — Tue, 29 Apr 2025 13:43:26 GMT

Hi @michael-wtds !

I'm glad you were able to get this to work. Have you tried the Date Comparison type instead?

Date: Allows dates with different formats to be matched and allows matching date to date/time fields (ignores the time).

You can check out the Comparison Types article.

Best,

Re: Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

michael-wtds — Tue, 29 Apr 2025 15:28:43 GMT

Hi @AnthonyValidity ,

Thanks for breaking this out to a new thread. Hopefully, this will help anyone else trying to do something similar.

Ideally, I would like to be able to match dates within a range of each other (e.g. +/- 30 days). So, something like 3/31/2025 would match 4/1/2025, as my regex solution only matched when the month and year were the same.

I did manually review any matches with the same year by changing the regex pattern to (\d{4}). So, at least I could manually update possible matches if needed.

Thanks once again!

All the best,
Michael

Re: Import Matching on Close Date on (MM-YYYY) RegExMatch Formula Help

AnthonyValidity — Tue, 29 Apr 2025 17:45:56 GMT

Great stuff, @michael-wtds! Love this use case. Thank you for sharing.