I have few transport timings. Basically, I just want to check their
ONTIME performance in terms of percentages. Is it possible to check
how many % of BEFORETIME and % of AFTERTIME?

My data is like this:

Date Trans_Name Schedule Actual Point Difference %
1-Mar E 1:35 1:41 A-B
1-Mar F 1:05 1:30 B-D
1-Mar A 0:50 1:57 D-B
1-Mar B 22:30 0:30 J-B
1-Mar D 22:15 23:02 B-D
1-Mar C 0:45 1:30 H-B
2-Mar B 1:35 1:47 A-B
2-Mar C 1:05 1:00 B-D
2-Mar E 22:30 23:55 J-B
2-Mar A 0:50 0:44 D-B
2-Mar F 22:15 22:25 B-D
2-Mar D 0:45 0:55 H-B
3-Mar D 1:05 0:45 B-D
3-Mar A 22:30 0:01 J-B
3-Mar B 1:35 1:35 A-B
3-Mar E 21:45 22:45 A-D
3-Mar F 0:45 0:55 H-B
3-Mar C 0:50 1:30 D-B

For instance, If I want to check the performance of point D-B in all
three days (1-mar to 3-mar) in terms of percentage (before time,
ontime, after time) and the deviation from the scheduled time, how
should I check it?

Thanks
Jakie

Can somebody help?

This will give the count of the "B-D" rows:
=COUNTIF(E2:E19,"b-d")

This will give a count of the B-D cells where the time in column D is greater
than the time in column C (late, right)?

=SUMPRODUCT(--(E2:E19="B-d"),--(C2:C19<D219))

So:

=SUMPRODUCT(--(E2:E19="B-d"),--(C2:C19<D219)) / COUNTIF(E2:E19,"b-d")

Will be the percent late (format it as percent if you like).

But I'm not sure if you have times in those cells or dates and times (just
formatted to show time).

If you have date/times, then we're done.

But if you only have times, this kind of thing will be ambiguous:

23:59 0:01

Was it 2 minutes late or 23 hours 58 minutes early?

If you have those two times in A1:B1, you could use:
=B1-A1+(A1>B1)
To get the 2 minute difference.
But this assumes that B1 is always after A1. And that doesn't work in your
case.

Maybe you could set a limit--no flight is every more than 4 hours late or
early???

=====
If I were doing it, I'd really try to include the dates and times in the data.

ps--about the =sumproduct() formula:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html






(E-Mail Removed) wrote:
>
> I have few transport timings. Basically, I just want to check their
> ONTIME performance in terms of percentages. Is it possible to check
> how many % of BEFORETIME and % of AFTERTIME?
>
> My data is like this:
>
> Date Trans_Name Schedule Actual Point Difference %
> 1-Mar E 1:35 1:41 A-B
> 1-Mar F 1:05 1:30 B-D
> 1-Mar A 0:50 1:57 D-B
> 1-Mar B 22:30 0:30 J-B
> 1-Mar D 22:15 23:02 B-D
> 1-Mar C 0:45 1:30 H-B
> 2-Mar B 1:35 1:47 A-B
> 2-Mar C 1:05 1:00 B-D
> 2-Mar E 22:30 23:55 J-B
> 2-Mar A 0:50 0:44 D-B
> 2-Mar F 22:15 22:25 B-D
> 2-Mar D 0:45 0:55 H-B
> 3-Mar D 1:05 0:45 B-D
> 3-Mar A 22:30 0:01 J-B
> 3-Mar B 1:35 1:35 A-B
> 3-Mar E 21:45 22:45 A-D
> 3-Mar F 0:45 0:55 H-B
> 3-Mar C 0:50 1:30 D-B
>
> For instance, If I want to check the performance of point D-B in all
> three days (1-mar to 3-mar) in terms of percentage (before time,
> ontime, after time) and the deviation from the scheduled time, how
> should I check it?
>
> Thanks
> Jakie

--

Dave Peterson

Actually, I want to fillup the F and G column

For example, in F2, the value should be 0:06 as there as a difference
of 6 mins from the scheduled time.
and in G3, the % should be 5.94%, which is the delay in percentage.
I also want to add one more column "H" which will show only two
values, "On/Before time" and "After Time" therefore in H3, the value
should be "After Time".

If I can do so for all the 19 rows, I can draw a PIVOT.


> But I'm not sure if you have times in those cells or dates and times (just
> formatted to show time).

==>I just have times and NO date/times

> 23:59 0:01
>
> Was it 2 minutes late or 23 hours 58 minutes early?
>

==>It is 2 minutes late

> Maybe you could set a limit--no flight is every more than 4 hours late or
> early???

==>No flight is 5 hours late and No flight is 1 hour early is what we
can set.

Thanx

First, Excel will only show negative times if you have a setting toggled.

In xl2003, it's under
Tools|Options|Calculation tab|check 1904 data system

Be aware that if you have dates in this workbook, they'll change by 4 years and
1 day. And sharing data that contains dates between workbooks that have
different base dates is troublesome, too.

But I put this in F2:
=(D2-C2)+((D2-C2<-20/24))
and dragged down.

I used this as my test data (and my results are shown, too):

Date Trans_Name Schedule Actual Point Difference
02-Mar E 01:35 01:41 A-B 00:06
02-Mar F 01:05 01:30 B-D 00:25
02-Mar A 00:50 01:57 D-B 01:07
02-Mar B 22:30 00:30 J-B 02:00
02-Mar D 22:15 23:02 B-D 00:47
02-Mar C 00:45 01:30 H-B 00:45
03-Mar B 01:35 01:47 A-B 00:12
03-Mar C 01:05 01:00 B-D -00:05
03-Mar E 22:30 23:55 J-B 01:25
03-Mar A 00:50 00:44 D-B -00:06
03-Mar F 22:15 22:25 B-D 00:10
03-Mar D 00:45 00:55 H-B 00:10
04-Mar D 01:05 00:45 B-D -00:20
04-Mar A 22:30 00:01 J-B 01:31
04-Mar B 01:35 01:35 A-B 00:00
04-Mar E 21:45 22:45 A-D 01:00
04-Mar F 00:45 00:55 H-B 00:10
04-Mar C 00:50 01:30 D-B 00:40

You can add a formula in the next column that looks at column F:
=if(f2<=0,"On/Before time","After Time")

I'm not sure what a percentage will give you when you're working with arrival
times. If you had total flight(?) time, it would make sense to me.


(E-Mail Removed) wrote:
>
> Actually, I want to fillup the F and G column
>
> For example, in F2, the value should be 0:06 as there as a difference
> of 6 mins from the scheduled time.
> and in G3, the % should be 5.94%, which is the delay in percentage.
> I also want to add one more column "H" which will show only two
> values, "On/Before time" and "After Time" therefore in H3, the value
> should be "After Time".
>
> If I can do so for all the 19 rows, I can draw a PIVOT.
>
> > But I'm not sure if you have times in those cells or dates and times (just
> > formatted to show time).
>
> ==>I just have times and NO date/times
>
> > 23:59 0:01
> >
> > Was it 2 minutes late or 23 hours 58 minutes early?
> >
>
> ==>It is 2 minutes late
>
> > Maybe you could set a limit--no flight is every more than 4 hours late or
> > early???
>
> ==>No flight is 5 hours late and No flight is 1 hour early is what we
> can set.
>
> Thanx

--

Dave Peterson