Countif date in range exceeds date in other range excell 2013

1
votes

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.

1
daterangeexcel-2013countif
Well, first thing is that your Dates are not Excel dates. Excel recognizes dates in the format MMDDYYYY and you have yours as DDMMYYYY so it's evaluating them as text instead of dates. Then the countif in the second part, the criteria, I think it's only taking the last value of the range instead of the whole range for the comparison and not comparing each row across. - Mike
If you convert all of the cells to be in a date format then it works if you also update the formula to be =COUNTIF(F1:F10,">"&E1:E10)-1 - Mike
Did you get this working? - Mike

1 Answers

0
votes

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))