You are a genius and I bow to you!! It worked like a charm. Thanks!!!



"David Biddulph" wrote:

Yes, you did something wrong. you've got the cell formatted as a number (a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/ Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).
--
David Biddulph

"JICDB" wrote in message
...
This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this formula
give me -0.009027777. Did I do something wrong?

"JE McGimpsey" wrote:

One way:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

or, if the times may span midnight,

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

In article ,
JICDB wrote:

I have searched for quite some time but can't seem to find the same
problem.
I have a file containing 50,000 lines containing scheduled times and
actual
times but the catch is that the original source formats the time as 630
for
6:30 or 1350 for 1:50pm. I am trying to subtract the actual time from
the
scheduled time to determine how many minutes the bus was late.

I tried adding leading zeros and subtracting the left 2 digits from
each
other, but that didn't work. I tried a variey of formulas listed here
but
none worked. Any ideas?