now() sumproduct.

[Guest]

I used to type enter time manually as under in coloumn B and D

Clients Check In Clients Check out
15 12:24 PM 23 2:34 PM

Then i used this as a time stamp.

=IF(C1178="","",IF(G1178="",NOW(),G1178))

etc etc to stamp time when i enter values in coloumn A and C.

now there is a sumproduct function that uses cell say B2 to calculate number
of clients checked in at that time..

sumproduct(B2:B500=E2)*(A2:A500)

This used to work manually, until i used time stamp now..which gives out
result as 23/09/2005 5:09:36 PM and not 5:09 PM.

So sumproduct cant find 5:09 Pm in coloumn B.
TIA.

 

[Bob Phillips]

Try

=sumproduct((MOD(B2:B500,1)=E2)*(A2:A500))