COUNTIFS on dates returned from MYSQL Query

[NickGB]

I'm struggling to get COUNTIFS to work when the dates I am tying to count are returned from a MYSQL table.

The formula I have used is:

=COUNTIFS(Smart!$Q$2:$Q$316,">"&(D12),Smart!$Q$2:$Q$316,"<"&(E12))

The actual dates are within the MYSQL table

=Table_Query_from_Primus[[#Headers],[Report_Forecast_Pre_nomina_Pre_nomina_date86]]

If I do a simple =Smart!$Q$2 if returns the date fine however as soon as I try and work in the COUNTIFS function it does not count.

I'm sure this is me not thinking this through properly but any help would be greatfull as I'm trying to create a dashboard.

 

[Asala42]

It's possible excel is reading those dates as text, which is a common problem with data coming into excel from an outside source.

You can handle that by just using a SUMPRODUCT instead of a COUNTIF.

=SUMPRODUCT(--(1*Smart!$Q$2:$Q$316>D12),--(1*Smart!$Q$2:$Q$316< E12))

[edit] I had to add an extra space between the "<" and "E12" to get the formula to display properly in the forum. Just remove that space when you test it.

 

[NickGB]

Thanks for the response Asala!

I removed the space (and the 1*before Smart! as this was causing a #value error) and unfortunately it still returns a 0 value.

I reduced the range and the search dates in D12 & E12 to ensure that it should at least return a count of 1 but still didnt pick it up.

I have also just tried counting the CELLS and this returned a 0

I have also just done =ISNUMBER(Smart!$Q$2) and it returns as FALSE and changed the cell to a (Q2) to a number and it returns as a FALSE.

Any ideas?

 

[NickGB]

A break through of sorts...

If I double click the cell in which the data is returned from the MYSQL query it then the function works against it.

If you dont then it doesnt.

 

[Asala42]

This confirms to me that excel is viewing these dates as text.

Usually you can resolve this kind of thing by doing a simple mathematical action on the text data (multiply the date by 1 or add zero to it). That's what the "1*" was for. If this is giving you a #VALUE error then that tells me that's not enough.

Test this out:

=SUMPRODUCT(--(1*CLEAN(TRIM(Smart!$Q$2:$Q$316))>D12),--(1*CLEAN(TRIM(Smart!$Q$2:$Q$316))< E12))

[edit] again - remove the extra space before the "E12" reference at the end

 

[NickGB]

Hi Asala

Unfortunately this didn't work either.

Would sharing the sheet be helpful?

 

[Asala42]

Post a sample of these dates.

 

[NickGB]

Planning_Decision_Actual_Planning_Nominated_date164

18/12/2014
18/11/2014
06/10/2014
21/01/2015
24/04/2014

23/04/2014
22/10/2014
23/04/2014
19/01/2015

04/07/2014

23/04/2014

20/08/2014


07/10/2014
05/11/2014

04/08/2014

14/01/2015
28/09/2014


14/01/2015
30/09/2014
23/07/2014
01/09/2014

I'm not quite sure this is what you want...

 

[NickGB]

Thought I would let you know I have worked it out(ish)...

By using the MySQL for Excel Add-In rather than importing the data through Microsoft Query the cells are manageable and I can COUNTIFS happily, or so it would seem at the moment.

Thanks for looking into it though Asala, the sumproduct is usable in the sheet as well so your help is appreciated.

 

[Asala42]

I'm glad you have it working. Those look like valid dates to me, is that how they also appear to be getting pulled from MySQL? A possibility might have been that excel was trying to read those dates as "mm/dd/yyyy" instead of "dd/mm/yyyy".