Countifs between date range when looking at a table , not range

[Godders199]

Hello, I am trying to get the following formula to work on a table that it imported into excel from a sequel report and loads as a table

=COUNTIFS(checksmart!$M:$M,'Mortgage RM Checks'!$A2,checksmart!$AP:$AP,"MPM",checksmart!$L:$L,">="&Instructions!$E$5,checksmart!L:L,"<="&Instructions!$E$6,checksmart!$T:$T,"",checksmart!$AC:$AC,"")

so it is counting any dates between the dates in E5 and E6 in column L where columns T an AC are blank.


however it does not seem to identify the dates in the table, if i change column L via text to columns it does, but some the date format is not consistant it seems to switch between 01/06/2018 and 06/01/2018.

If i just change formula to count non blank cells in calulates the correct number, its purely when i ask it to count between to dates it returns zero.

DO i need to change something in the formula when looking at a table?

Any help would be appreciated

 

[steve the fish]

Ok so excel is not seing your dates as dates. Did you know you can do text to columns specifying the dates format? Text to columns-delimited-next-next-date radio button specifying correct format.

 

[Godders199]

did not know, will give it a go

 

[Godders199]

OK, I have added the following code,

With ActiveSheet.UsedRange.Columns("l").Cells
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "dd-mm-yyyy"
End With

which in the majority sorts my problem, however a it does not seem to capture every row for example
[TABLE="width: 124"]
<tbody>[TR]
[TD="align: right"]02-06-2018[/TD]
[/TR]
[TR]
[TD]16/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]02-01-2018[/TD]
[/TR]
[TR]
[TD]13/02/2018[/TD]
[/TR]
[TR]
[TD]13/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]02-02-2018[/TD]
[/TR]
[TR]
[TD="align: right"]02-12-2018[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
so it has not formated 3 dates and therefore not counting these, is my code wrong?

[TABLE="width: 124"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]16/02/2018[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]