ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   On time arrival (https://www.excelbanter.com/excel-discussion-misc-queries/137514-time-arrival.html)

Richard April 2nd 07 04:44 PM

On time arrival
 
A1: Scheduled arrival time.
B1: Actual arrival time.

In C1, I would like to compute the time difference between scheduled and
actual arrival time. I'm golden as long as the actual arrival is earlier
than the scheduled time. When B1 is later than A1, I just get a result of
#######.

Is there a way for this time to be displayed as a negative? Any other
suggestions for identifying late arrivals? Please no visual basic. I'm a
rookie at this.

Thanks

Bob Phillips April 2nd 07 04:52 PM

On time arrival
 
One way

=IF(B1A1,B1-A1,TEXT(MOD(B1-A1,1),"-hh:mm"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Richard" wrote in message
...
A1: Scheduled arrival time.
B1: Actual arrival time.

In C1, I would like to compute the time difference between scheduled and
actual arrival time. I'm golden as long as the actual arrival is earlier
than the scheduled time. When B1 is later than A1, I just get a result of
#######.

Is there a way for this time to be displayed as a negative? Any other
suggestions for identifying late arrivals? Please no visual basic. I'm a
rookie at this.

Thanks




David Biddulph[_2_] April 2nd 07 05:42 PM

On time arrival
 
But that would show -21:00 where the result of the calculation is -3:00.

Might I suggest changing Bob's formula to
=IF(B1A1,B1-A1,TEXT(A1-B1,"-hh:mm"))
--
David Biddulph

"Bob Phillips" wrote in message
...
One way

=IF(B1A1,B1-A1,TEXT(MOD(B1-A1,1),"-hh:mm"))


"Richard" wrote in message
...
A1: Scheduled arrival time.
B1: Actual arrival time.

In C1, I would like to compute the time difference between scheduled and
actual arrival time. I'm golden as long as the actual arrival is earlier
than the scheduled time. When B1 is later than A1, I just get a result
of
#######.

Is there a way for this time to be displayed as a negative? Any other
suggestions for identifying late arrivals? Please no visual basic. I'm a
rookie at this.

Thanks







All times are GMT +1. The time now is 01:00 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com