countif of cell with both date & time

  • Thread starter Thread starter Gricey
  • Start date Start date
G

Gricey

Hello All,

I wish to do a countif on the column title 'ARRIVALTIME', but als
group it into time windows aswell, such as 08:00-08:30, 08:30-09:0
etc.

What I am looking for is something like:

Countif date = 08/03/2003 AND time is >08:00 but less than <08:30.

Please note the date and time's are in the one cell. I have tried usin
LEFT & RIGHT functions to split the cells, but then I get i
represented as a number and have had trouble converting it back t
either a date or time format.

ARRIVALTIME
08/03/2004 08:22
08/03/2004 08:25
08/03/2004 08:46
08/03/2004 08:54
08/03/2004 08:5
 
Hi
try
=SUMPRODUCT(--(INT(A1:A100)=DATE(2003,8,3)),--(MOD(A1:A100,1)>8/24),--(
MOD(A1:A100,1)<8.5/24))
 
Back
Top