work out how many trucks came in between the hours of 9am and 3pm

  • Thread starter Thread starter hotlh
  • Start date Start date
H

hotlh

hi there l am after a formula which will calculate how many trucks came in
from the hours of 9 am and 3 pm on any day. the main information is in sheet
1 and l want the value to return to sheet 2. How do l do this? and what
formula is best to use.
A:A time in time out transport coompany amount of
pallets

1-Apr 4:00 5:26 LEOCATAS WJT982 BRIAN B 68 TATURA 656223 T15.02 68 1
1-Apr 4:06 5:45 LEOCATAS UMV991 DAVE B 68 TATURA 656224 T13.02 68 1
1-Apr 5:08 6:00 HUNTERS QZW243 RODNEY B 68 TATURA 656228 T17.17 68 1
1-Apr 5:50 6:25 LEOCATAS TIP994 BILL S 22 TATURA 656233 T21.57 22 1
1-Apr 5:50 6:25 LEOCATAS TIP993 PETER S 22 TATURA 656232 T21.57 22 1
1-Apr 5:50 6:40 LEOCATAS UZJ995 ANTHONY B 51 TATURA 656225-656231 T1.63T33.34 51 1
1-Apr 7:20 8:50 LEOCATAS WMK995 JIM B 44 TATURA 656214 T32.56 44 1
1-Apr 16:45 16:25 MITCHELLS VV34HO BRENDON B 35 TATURA 656229 T33.62 35 1
1-Apr 19:30 20:21 GATTUSO XIZ235 BRETT B 38 TATURA 656635 T33.78 38 1
1-Apr 19:31 20:21 GATTUSO GTP010 STEWEY B 38 TATURA 656634 T35.66 38 1
1-Apr 19:31 22:05 GATTUSO GTP017 PETER B 68 TATURA 656641 T13.06 68 1
1-Apr 19:45 21:07 GATTUSO GTP013 GARY B 68 TATURA 656642 T13.21 68 1
1-Apr 23:55 0:50 UNILEVER VV34HOI BRENDAN B 38 TATURA 656938 T34.69 38 1
6-Apr 4:15 5:31 LEOCATAS UZJ995 DAVE B 34 TATURA 656636 T31.86 34 1
6-Apr 5:00 6:00 LEOCATAS WMK995 JIM B 44 TATURA 656633 T32.56 44 1
6-Apr 5:05 6:00 HUNTERS QZW243 RODNEY B 68 TATURA 656990 T14.87 68 1
6-Apr 5:40 6:50 LEOCATAS UMV991 RONNY B 68 TATURA 656643 T15.21 68 1
6-Apr 5:45 6:50 LEOCATAS UHT987 ADRIAN B 34 TATURA 656936 T32.56 34 1
6-Apr 5:50 6:45 LEOCATAS TIP993 PETER S 31 TATURA 656645-656937 T.87T22.52 31 1
6-Apr 5:55 7:10 LEOCATAS TIP994 BILL S 22 TATURA 656637 T18.08 22 1
7-Apr 19:15 20:00 GATTUSO XIX235 PETER B 68 TATURA 657636 T14.99 68 1
7-Apr 19:25 20:40 GATTUSO GLS904 DAVID B 68 TATURA 657635 T15.12 68 1
7-Apr 19:27 20:05 GATTUSO GTP012 DAVE B 35 TATURA 657624 T32.95 35 1
7-Apr 19:55 20:36 GATTUSO GTP015 MATT S 25 TATURA 657626 T23.11 25 1
7-Apr 20:20 23:45 SPD QWF171 JOHN B 34 TATURA 657867 33.08 34 1
8-Apr 4:00 6:03 LEOCATAS UZJ995 DAVE B 34 TATURA 657631 T32.35 34 1
8-Apr 4:00 5:40 LEOCATAS UHT987 ADRIAN B 32 TATURA 657860 T28.50 32 1
8-Apr 5:10 5:59 HUNTERS QXW243 RODNEY B 34 TATURA 657627 T31.51 34 1
8-Apr 5:34 6:14 LEOCATAS TIP993 TIM S 23 TATURA 657814 T20.72 23 1
8-Apr 5:35 6:15 LEOCATAS TIP994 BILL S 38 TATURA 657866 T8.22 38 1
8-Apr 6:25 7:13 KEATINGS WOV491 MARK B 34 TATURA 657819 34 1
8-Apr 14:20 15:21 HUNTERS WIL926 STEVE B 35 TATURA 658043 35 1
8-Apr 17:25 18:10 GATTUSO GTP011 PETER B 35 TATURA 658101 T33.28 35 1
 
In sheet2, you enter something like:

=countif(sheet1!b:b,">="&time(9,0,0))-countif(sheet1!b:b,">"&time(15,0,0))

Assumes the time you want to check is in column B.

Regards,
Fred
 
Back
Top