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.


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