D
Dale G
I have more info in my other post (Tracking sheet help) & (sumproduct
function, Bob P please see tracking sheet help).
I'm using this in Sheet 2 (LTC)
=IF(D3="","",D3-C3)
and this in sheet 3 (OTP)
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<>""))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"),--(LTC!$E$3:$E$192<>""))
I"m counting vehicles that pass a location. Sheet 2 (LTC) column C is the
time they are scheduled to pass. Column D is the time I enter that they
actually pass.
Column E sheet 2 is set to auto calculate the difference, =IF(D3="","",D3-C3)
Sheet 3 is used for ON TIME PERFORMANCE (OTP). If they pass from within 0 to
10 minutes I count them in column B of sheet 3 (OTP), and if they pass 11 or
more minutes late I count them in column C of sheet 3 (OTP).
The left 4 digits in column F Sheet 2 (LTC) are the same as column A sheet 3
(OTP)
My problem is the formula is not placing the count in the right column if
the difference = 10 & and if I change the formula to 11 the same occurs.
Would there be a way to have Sheet 3 set up to do the math. Similar to the
formula on sheet 2. =IF(D3="","",D3-C3) and if the total = under 0 to 10
minutes it will be entered in Sheet 3 column B and if the total was 11
minutes or more the count would be entered in sheet 3 column C?
(I said Under because sometimes they are allowed to pass before there
scheduled time so I end up with a negative that I have to manually enter (3-)
or (4-),(5-) in column E sheet 2, which further complicates the sheet, I
don't think there is a way to count or show negative time).
Hope this makes some sort of sense.
Example sheet 2(LTC)
A B C D E
F
Run Vehicle Time Actual Diff Schedule
924 0 4:48 4:59 0:11 535S0448
208 0 5:13 5:23 0:10 401S0459
904 0 5:18 5:28 0:10 535S0518
908 0 5:25 511S0518
Example sheet 3 (OPT)
A B C
Route 0-10 +10
401n 0 0
401s 0 0
402n 0 0
402s 0 0
511n 0 0
511s 0 0
535n 0 0
535s 0 0
function, Bob P please see tracking sheet help).
I'm using this in Sheet 2 (LTC)
=IF(D3="","",D3-C3)
and this in sheet 3 (OTP)
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192<=--"0:10"),--(LTC!$E$3:$E$192<>""))
=SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192>=--"0:10"),--(LTC!$E$3:$E$192<>""))
I"m counting vehicles that pass a location. Sheet 2 (LTC) column C is the
time they are scheduled to pass. Column D is the time I enter that they
actually pass.
Column E sheet 2 is set to auto calculate the difference, =IF(D3="","",D3-C3)
Sheet 3 is used for ON TIME PERFORMANCE (OTP). If they pass from within 0 to
10 minutes I count them in column B of sheet 3 (OTP), and if they pass 11 or
more minutes late I count them in column C of sheet 3 (OTP).
The left 4 digits in column F Sheet 2 (LTC) are the same as column A sheet 3
(OTP)
My problem is the formula is not placing the count in the right column if
the difference = 10 & and if I change the formula to 11 the same occurs.
Would there be a way to have Sheet 3 set up to do the math. Similar to the
formula on sheet 2. =IF(D3="","",D3-C3) and if the total = under 0 to 10
minutes it will be entered in Sheet 3 column B and if the total was 11
minutes or more the count would be entered in sheet 3 column C?
(I said Under because sometimes they are allowed to pass before there
scheduled time so I end up with a negative that I have to manually enter (3-)
or (4-),(5-) in column E sheet 2, which further complicates the sheet, I
don't think there is a way to count or show negative time).
Hope this makes some sort of sense.
Example sheet 2(LTC)
A B C D E
F
Run Vehicle Time Actual Diff Schedule
924 0 4:48 4:59 0:11 535S0448
208 0 5:13 5:23 0:10 401S0459
904 0 5:18 5:28 0:10 535S0518
908 0 5:25 511S0518
Example sheet 3 (OPT)
A B C
Route 0-10 +10
401n 0 0
401s 0 0
402n 0 0
402s 0 0
511n 0 0
511s 0 0
535n 0 0
535s 0 0