Extracting time from day

Posted by Tom on April 27, 2001 12:53 PM

Heres a little problem I can't get past.

On one sheet I have a log for documents that are submitted to me. I have a VB script which places a time/date stamp in column C. On a seperate sheet, I need to count how many came in on a certain day. Heres what I was using:

=COUNTIF(sheet1!$C$3:$C$102,N4)

The problem is the range of C3 to C102 in date:time format (eg. 4/10/2001 2:48:27 PM) and N4 is only in date format(eg. 4/10/2001). The thing won't count it because of, I'm pretty sure, the added time.

Any suggestions?

Thanks on advance

-Tom

Posted by cpod on April 27, 2001 1:11 PM

You could either add another column of data using the Trunc function, which will set the decimal portion of the date serial to zero, thus eliminating the time, and then do your countif on that data - or you could use and array function which must be entered using Control+Shift+Enter:

=SUM(IF(TRUNC(sheet1!$C$3:$C$102)=N4,1,0))

Posted by Mark W. on April 27, 2001 3:56 PM

Yet another array formula solution...

{=COUNT(MATCH(TRUNC(sheet1!$C$3:$C$102),N4,0))}