Countif date in range exceeds date in other range excell 2013

Question

I want to count the amount of times a date in one column exceeds the date in another column. I already seem to have found a way to do this, but I would like to know what exactly is wrong with my first formula.

17-4-2016 . . . 21-3-2016

17-6-2016 . . . 11-11-2016

17-6-2016 . . . 15-4-2016

23-3-2017 . . . 1-4-2016

23-3-2017 . . . 23-3-2016

23-3-2017 . . . 26-9-2016

23-3-2017 . . . 1-4-2016

23-3-2017 . . . 12-4-2016

23-3-2017 . . . 11-4-2016

23-3-2017 . . . 15-4-2016

My own formula: =COUNTIF(B1:B10;">"&A1:A10) , which gives me a value of 0, while 1 date clearly exceeds the date in the corresponding cell.

I found this formula to work: =SUMPRODUCT(--(B1:B10>A1:A10)) , which gives the correct value of 1.

I would like to know the error in my own formula and how the second one does work.

Answer 1

You need to convert all of the cells values into a Date format that Excel will recognize and treat as a Date. Right now it is treating most of them as text. Once you do that a small change to your formula will get the results that you want, even if you change some of the dates to have more that 1 that is later in column "B".

=COUNTIF(B1:B10,">"&A1:A10)-1

Something like this will convert them into Dates:

=CONCATENATE(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-(FIND("-",A1)+1)),"/",LEFT(A1,FIND("-",A1)-1),"/",RIGHT(A1,4))