LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Issue with numbers and Time

First thank you to everyone that helps all us that are stuck!
Second, this will sound strange, but this is what the customer
requests.
Issue is that when subtracting the two times in decimal time, I get a
different number than the calculation should be.
example: 0.020833333 - 0.020833333 = -7.28584E-17 Should be ZERO!


Four variables: Start Time, Finish Time, Quanity, Speed
Start and Finish times are numbers with the cells formatted to #":"##
I.E. 12:15 pm is 1215, 1:15am is 115
Quanity is a number representing amount of pieces produced
Speed is how fast each each piece is produced

Getting the amount of time between Start(I8) and Finish(J8) times:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE (LEFT(I8,1)&":"&RIGHT

(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))

Getting the amount of time from quanity(H8) and speed(M8):
=((M8*H8)/60)/(24*60)

When either subtracting these two calculation against each other:
=IF(J8="","",IF(I8="","",(IF(LEN(J8)3,TIMEVALUE(L EFT(J8,2)&":"&RIGHT
(J8,2)),TIMEVALUE(LEFT(J8,1)&":"&RIGHT(J8,2))))+(I F((IF(LEN(I8)
3,TIMEVALUE(LEFT(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE (LEFT(I8,1)&":"&RIGHT

(I8,2))))(IF(LEN(J8)3,TIMEVALUE(LEFT(J8,2)&":"&R IGHT(J8,2)),TIMEVALUE
(LEFT(J8,1)&":"&RIGHT(J8,2)))),0.5,0))-(IF(LEN(I8)3,TIMEVALUE(LEFT
(I8,2)&":"&RIGHT(I8,2)),TIMEVALUE(LEFT(I8,1)&":"&R IGHT(I8,2))))))-
((M8*H8)/60)/(24*60)

And these are the variables:
Start 1230, Finish 130, Speed 60, Quanity 30

I get the value:
-7.28584E-17

And it should be:
0

HELP!!! I don't know if I am doing something wrong and there is most
likely a shorter way of performing this calculation.
I am open to easier ways to calculate, Functions, whatever.
Hope I didn't lose anyone on this.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time issue MJKelly Excel Programming 6 November 19th 08 12:30 PM
Time issue with Day Format K1KKKA Excel Discussion (Misc queries) 1 June 6th 07 10:24 AM
odd sort issue with numbers Lilivati Excel Programming 2 July 6th 06 07:27 PM
Less than numbers issue please help DKY[_88_] Excel Programming 2 September 21st 05 08:58 PM
Run Time Issue Steph[_3_] Excel Programming 2 May 27th 04 08:07 PM


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"