Regex in Excel not matching a date when that date has a slash in it

Question

I need to find where a date value is later than the year 1911. Some of these values aren't uniform: some are Jan-1910 (beneath the formatting this is 01/01/1910, others are 1910-1914. Dates before 1900 have to be written manually as Jan 1899 due to Excel date limitations.

I have this regex being put into a formula:

=REGEXTEST(B2, "191[1-9]")

when I use it on a selection of values I get:

So the regex works for isolated numbers, doesn't work for dates of the format Jan-09 (internally 01/01/1909), and does work for dates with dots instead of slashes.

On regex101.com, it works as intended.

Can you tell me why the last one 01.01.1912 is working for you and not the one which are real true dates? If you format the cell range as Text then 1911,1912,Jan-11,Jan-12 will return TRUE for you. I am not an expert in Regex but I tested in Excel that when you turn them text it gives the desired output as expected but when those are real numbers thats how dates are stored in excel returns false. 1911 and 1912 or if you write 1915 it will also give you desired output because those are one number, there is nothing behind the scenes going on for those. — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Aug 3 at 15:37
I tried something like this, which worked though, but there should be more proper way of doing this: =REGEXTEST(IF(CELL("format", B2)="G", B2, TEXT(B2, "m/d/e")), "191[1-9]") — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Aug 3 at 15:44

Chris Maurer · Accepted Answer · 2025-08-03 16:00:23Z

0

What you have is a representation problem, not a RegExp problem.

Regexp_test() accepts a string as its first argument, and if it is not given in that format it tries to convert it to one. Your first 4 examples are integers that get converted directly to strings just as you see. But the next 5 are Dates - Excel converts date-times to floating point numbers, so when you input "01/01/1912” Excel turned that internally into 4384.0 while continuing to display it as 01/01/1912. When you passed it to Regexp_Test it converted that floating point number to the string "4384.0", which of course does not match your pattern.

Make sure you have turned all your dates to strings before using Regexp_Test and you won't have problems.

answered Aug 3 at 16:00

Chris Maurer

2,9321 gold badge13 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brandon Lee · Accepted Answer · 2025-08-03 16:22:56Z

0

To supplement the answer @chris-maurer gave, here is your spreadsheet with an added C column with the internal representation of each date

To convert your date to a string, use TEXT(A1,"dd/mm/yyyy") etc.

answered Aug 3 at 16:22

Brandon Lee

1012 silver badges14 bronze badges

Collectives™ on Stack Overflow

Regex in Excel not matching a date when that date has a slash in it

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related