google sheets - Getting the difference between 2 dates

Question

So for example I have a string of 11/01/2021 - 15/01/2021 in cell M3. I want to make it so whenever I manually type that in, I get 4 in return (the amount of days in between those 2 dates).

Currently I'm using this formula: =IF(ISBLANK(M3)=FALSE, DATEDIF(TEXT(DATEVALUE(ARRAY_CONSTRAIN(SPLIT(M3, " - "), 1,1)), "dd mm yyyy"), TEXT(DATEVALUE(SUBSTITUTE(REGEXREPLACE(M3,ARRAY_CONSTRAIN(TO_TEXT(SPLIT(M3, " - ")), 1,1), "") , " - ", "")), "dd mm yyyy"), "D"),)

Problem with that is, if the days value is bigger than 12 it just breaks and I get the error DATEVALUE parameter '15/01/2021' cannot be parsed to date/time.

The Locale is set to United States and the Display Language is set to English.

Any help would be much appreciated, thank you.

Answer 1

try:

=IFERROR(INDEX(DAYS(INDEX(SPLIT(M3:M, " - "),,2), 
                    INDEX(SPLIT(M3:M, " - "),,1))))