Countifs formula not picking up dates, when reference columns show as dates?!

[dezibluenose]

I’ve a strange issue here – strange for Me anyway.

On one sheet of my excel file, dates aren’t registering as dates even though they are in date format?!? This particular sheet pulls data from an SQL server. From that I'm assuming my problem is most likely NOT an issue with my formula but an issue with how the data is pulled into my excel file.


To explain more, I’m doing a date count that references another sheet on my excel file. I’m checking a customer number and asking my formula to return how many issues we’ve had within a certain date window. This second sheet has information from the last 5 years so I use the following formula to check for issues in this financial year:-

=COUNTIFS(Sheet2!B:B, Sheet1!A:A, Sheet2!AT:AT,">=01/04/2024", Sheet2!AT:AT,"<=01/05/2025")

Sheet 1 is my main sheet with customer details. Column A has customer numbers
Sheet 2 is a report that gets pulled from an SQL Server. Column B has customer numbers and column AT has the dates I need to search.


Dates shown on Sheet 2 don’t seem to come through as dates when pulled from the SQL server. They look like dates on the columns, both format cells and the number format ribbon show the format for those columns as dates but until I copy them from the file, paste them into notepad then copy/paste them back in, my formula doesn’t work. Until I do the copy/paste, the formula doesn't register them as dates.

I've included a screen dump below of how they look on a filter before and after doing my copy and paste routine.

Data on Sheet 2 is pulled from an SQL server, and as I said earlier, I'm assuming the issue lies there and there's nothing I can do to my formula to fix this. That report comes from somewhere else and I guess unless there is a change there - and sadly I don't have access to change that report - I have to carry on doing my manual fix for my formula.

I'm hoping though that someone has a way to fix my formula so it registers dates regardless of format. I'm open to any suggestions for fixing this issue that my excel knowledge is lacking. Thanks in advance.

 

[Fluff]

Are the dates from the SQL in dd/mm/yyyy format?

 

[dezibluenose]

Are the dates from the SQL in dd/mm/yyyy format?

While I've not got the admin access to check what the SQL report pulls, when I go onto the tab with the pulled data, it shows number format as dates and DD/MM/YYYY format.

I've screen dumped below what I see. Image on the left is before I copy/paste the data to note pad and back in - image on the right is after that. When I check the formatting, both are the same. The only difference is after pasting, the date changes from a left align in the column to the right.

 

[Fluff]

I'm not interested in the cell format, but the actual text values of the dates.

 

[Alex Blakenburg]

It would be better to clean up the source data. How are you getting the data into sheet2 ? VBA, PQ or some other way.
If none of those, how about adding a helper column to convert the text dates to real dates.

This might work for you but I wouldn't be using whole column referencing with it. Pick a number of rows that is bigger then you ever expect to need.
Note: Typically if you have >= to 01/04/2024 you would have < 01/05/2025 not <=.

20240704 CountIf Dates as Text dezibluenose.xlsx
	A	B
1	Customer No	Count
2	1	5
3	2	5
4	3	4
5	4	3
6	5	3
7	6	1
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=SUM((Sheet2!B2:B10000=A2)*(--Sheet2!AT2:AT10000>=DATEVALUE("01/04/2024"))*(--Sheet2!AT2:AT10000<=DATEVALUE("01/05/2025")))

 

[dezibluenose]

It would be better to clean up the source data. How are you getting the data into sheet2 ? VBA, PQ or some other way.
If none of those, how about adding a helper column to convert the text dates to real dates.

This might work for you but I wouldn't be using whole column referencing with it. Pick a number of rows that is bigger then you ever expect to need.
Note: Typically if you have >= to 01/04/2024 you would have < 01/05/2025 not <=.

20240704 CountIf Dates as Text dezibluenose.xlsx
	A	B
1	Customer No	Count
2	1	5
3	2	5
4	3	4
5	4	3
6	5	3
7	6	1
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=SUM((Sheet2!B2:B10000=A2)*(--Sheet2!AT2:AT10000>=DATEVALUE("01/04/2024"))*(--Sheet2!AT2:AT10000<=DATEVALUE("01/05/2025")))

That has worked perfectly - thank you so much.

As yourself and Fluff have rightly said, the issue is the source data file and I've got no access to that. This will fix my issue until a change is made on the source file. Thanks again to both you for your help.

 

[Fluff]

You can also use text to columns, to convert them into real dates. That is why I asked how the dates were formatted.

 

[Alex Blakenburg]

You're welcome. If you want to elaborate on how / what the process is to get the data into your workbook we may be able to suggest ways of converting the dates. The text to columns method is a manual method but if you are running a macro then you can add that to your macro. If you are using Power Query then its a simple matter to convert the dates.