Time, & Ceiling?

[Dale G]

I asked this question yesterday,
My spread sheet is used to track arrival times at a specific location. The
location has a scheduled arrival time in column C, in column D I enter the
actual arrival time when a vehicle arrives, and column E calculates the
difference.

I use the 1904 time system so I’ll be able to calculate and show negative
time. Column E is set with =IF(D3=â€â€,â€â€,D3-C3) this will have column E appear
blank until the actual time is entered.

Without =IF(D3=â€â€,â€â€,D3-C3)in column E and with =D3-C3 this will show the
negative of the scheduled time i.e. -4:59 or -18:44. The schedule time is
always present in column C.

The trouble I’m having now is; often a vehicle will arrive right on time
which will calculate like 0:00.
This is good, but after a few on time calculations some of will appear like
this -0:00, and that’s not good.

Would anyone know of a way to prevent the negative sign from appearing when
the arrival time is on time?

Pete_UK answered;
If you changed it to this:
=IF(D3=â€â€,â€â€,CEILING(D3-C3,1/24/60))
this will round your result up to the nearest minute, so this should
correct for -0:00.

Today I tried that, and it worked for the result of 0:00. But when the
result is a minus number less than 0:00 the formula will show #NUM. If
possible I need to have the actual negative number.
Any help is appreciated.

 

[Pete_UK]

Hello again, Dale.

Try this one out:

=IF(D3="","",SIGN(D3-C3)*CEILING(ABS(D3-C3),1/24/60))

Hope this helps.

Pete

 

[Dale G]

Pete, & David thanks,

Pete, it looked like =IF(D3="","",SIGN(D3-C3)*CEILING(ABS(D3-C3),1/24/60))
would work, but after about 20 entries this happened

Time Actual Diff
6:36 6:35 -0:02

David also your =IF(D3="","",TRUNC((D3-C3)*24*60)/24/60) looked good until
the 15th entry then

Time Actual Diff
5:43 5:40 -0:02

And after 25 entries

Time Actual Diff
6:44 6:40 -0:03

 

[Dale G]

David, thank you it works, over 50 entries and all went well.

It seems the trouble would occur when the next row had the same time.

Something like

Time Actual Diff

6:36 6:36 0:00

6:36 6:36 -0:00

Thanks again.