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 Ill 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 Im 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 thats 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.

Hello again, Dale.

Try this one out:

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

Hope this helps.

Pete

On Dec 19, 12:11*am, Dale G wrote:
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.

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)
--
David Biddulph

"Dale G" wrote in message
...
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, & 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" wrote:

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)
--
David Biddulph

"Dale G" wrote in message
...
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.



.

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)
--
David Biddulph

"Dale G" wrote in message
...
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" wrote:

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)
--
David Biddulph

"Dale G" wrote in message
...
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.



.

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.


"David Biddulph" wrote:

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)
--
David Biddulph

"Dale G" wrote in message
...
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" wrote:

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)
--
David Biddulph

"Dale G" wrote in message
...
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.



.



.