Thread: Using COUNTIF and dates of different formats

I have a table with the start time of a tour and the visitor's name. The start time is in m/d/yyyy h:mm:ss AM/PM format. On a separate portion of the spreadsheet I want to count the number of tours given by day. I tried simply using =COUNTIF(Table1[Start Time],G$2) where G2 is a date in m/d/yyyy format, but the count comes up as zero even when there were visitors on that day because each tour has a unique time of day. Is there anyway to use wildcards or something to make the count function properly? See the attached excel file.

Thanks

One way:

Cell F3: =SUMPRODUCT(--(DAY($B$3:$B$18)=DAY(F$2)),--(MONTH($B$3:$B$18)=MONTH(F$2)))

and copy across.


Regards, TMS

Or,

=COUNTIF(Table1[Start Time],">="&F$2)-COUNTIF(Table1[Start Time],">"&F$2+"23:59:59")

=SUMPRODUCT(--(TEXT(Table1[Start Time],"dmyyyy")=TEXT(F$2,"dmyyyy")))

Copy across

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save