Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Time issue | Excel Programming | |||
Time issue with Day Format | Excel Discussion (Misc queries) | |||
odd sort issue with numbers | Excel Programming | |||
Less than numbers issue please help | Excel Programming | |||
Run Time Issue | Excel Programming |