Categorize time ranges and calculate events within the time period

With helper column in "J" with =MOD(H2,1) copy down till end

"N4"

Formula:

Please Login or Register  to view this content.

For auto fill date
In K5, L5, M5 then drag down

=INT($K$4+M4)

=L4+"01:00"

=M4+"01:00"

Can you share some mock up data.

Hi

thanks for the reply.

I actually wanted to autofill the date as follows. if yardtime has 07-Jul-19 10:38:01 then to fill K4= 07-jul-19, L4=10:00, M4=11:00 as 10:38 will fall in within this time range. similarly , to analyse all times under yardtime and autofill date and time range.

Please manually mock up what you want in your workbook and post it again.

PL see file.
Column G and H were in text format. I changed into date time format.

In K4, L4,M4

=INT(MIN($H$2:$H$34))

=FLOOR(MOD(MIN($H$2:$H$34),1),"1:00:00")

=L4+"1:00"

In N4 copied down to column Q

=COUNTIFS($H$2:$H$34,">="&$K4+$L4,$H$2:$H$34,"<="&$K4+$M4,$F$2:$F$34,N$3,$I$2:$I$34,IF(N$2="Driver ID",O$2,N$2))

Thanks for feed back.