count ifs with date range from date time field

[Imran Azam]

HI Guys

i am doing a count ifs statement on the example data below

[TABLE="width: 261"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 492"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Stats
[/TD]
[TD] date
[/TD]
[/TR]
[TR]
[TD]Alex
[/TD]
[TD]Qualified
[/TD]
[TD]30/03/2019 01:18:01
[/TD]
[/TR]
[TR]
[TD]Alex
[/TD]
[TD]Qualified
[/TD]
[TD]01/04/2019 10:08:01
[/TD]
[/TR]
[TR]
[TD]Alex
[/TD]
[TD]Not Qualified
[/TD]
[TD]30/04/2019 02:01:01
[/TD]
[/TR]
[TR]
[TD]Alex
[/TD]
[TD]Qualified
[/TD]
[TD]29/04/2019 16:18:01
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]Not Qualified
[/TD]
[TD]26/001/2019 10:55:01
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The logic i am using is count Name if name equals Alex and stats Qualified and date range is between 01/04/2019 and 30/04/2019 then count name and divide this by the count of name where name is alex and date range is between 01/04/2019 and 30/04/2019. the answer should be 2/3

from previous post io did i used the following formula but now the date field has date and time this doesnt work

=COUNTIFS(A2:A10,"Alex",B2:B10,"Qualified",C2:C10,">="&DATEVALUE("4/1/19"),C2:C10,"<="&DATEVALUE("4/30/19"))

can any one help me correct this formula?

thanks guys

 

[CalcSux78]

I think your formula is fine.. It may be an issue where your dates/times are reading as text and not a date value.

 

[Imran Azam]

I think your formula is fine.. It may be an issue where your dates/times are reading as text and not a date value.

thanks, any idea how i can fix this?

 

[CalcSux78]

This would give you a start... Highlight all of your date/time data and run this:

Sub Text2Date()

Dim cel As Range
Dim arr As Variant
Dim aDate As Variant


For Each cel In Selection
    arr = Split(cel.Value, " ")
    aDate = Split(arr(0), "/")
    'assuming date is in mm/dd/yyyy format
    cel = DateSerial(aDate(2), aDate(1), aDate(0)) + TimeValue(arr(1))
Next cel
End Sub