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

 

[David Biddulph]

It's generally better to stay with the same thread, Dale, rather than
starting a new one.

Try =IF(D3="","",TRUNC((D3-C3)*24*60)/24/60)

 

[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

 

[David Biddulph]

You'll have to decide how to deal with rounding, because your times are
unlikely to calculate to numbers which Excel can store exactly.
Pete's solution was trying to round upwards (away from zero).
Mine was trying to truncate towards zero.
If you prefer to round to the nearest minute, try
=IF(D3="","",ROUND((D3-C3)*24*60,0)/24/60)

 

[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.