countif of cell with both date & time

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
 
F

Frank Kabel

Hi
try
=SUMPRODUCT(--(INT(A1:A100)=DATE(2003,8,3)),--(MOD(A1:A100,1)>8/24),--(
MOD(A1:A100,1)<8.5/24))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Conditional Time Calculation 10
Aggregate the time data (mm/dd/yyyy:hh:mm:ss) into the 3 hours range 0
Calculating Work Time 7
IF ARRAY with multiple conditions. 1
Conditional Formatting 0
When building a chart Dates are been included that are not in thedate range 2
Return Time for Duplicate Files & Times 4

Top