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

Name Stats date Alex Qualified 30/03/2019 01:18:01 Alex Qualified 01/04/2019 10:08:01 Alex Not Qualified 30/04/2019 02:01:01 Alex Qualified 29/04/2019 16:18:01 James Not Qualified 26/001/2019 10:55:01 <tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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