Formula for time.

[Nicole]

Hi,

I would like to be able to work this into a formula:

Units (20) x Cycle time (1.82) = Minutes (36.4) and then + Start Time ie.
7:00am = the Finish time??

Does anyone have a formula that I could use?

Thank you

 

[Jacob Skaria]

Hi Nicole

Try the below

Col A Col B Col C Col D
StartTime Units CycTime EndTime
7:30 AM 20 1.82 =formula

=A2+(B2*TIME(0,INT(C2),MOD(C2,1)*60))

PS: I assume cell A2 is having the start time in excel date/time format. To
enter the time use short cut is (Ctrl + Shift + semicolon) and then edit to
suit...

If this post helps click Yes

 

[David Biddulph]

Haven't you lost a few seconds there, Jacob? You've added 36:20 instead of
the 36:24 that Nicole wanted.

Try =A2+(B2*C2/(24*60))

 

[Jacob Skaria]

Thanks David; For the example I took cycle time as 1.82 (1 minutes and 0.82
seconds) . I didnt notice the 36.4..Am I missing something here??

Col A Col B Col C Col D
StartTime Units CycTime EndTime
7:30 AM 20 36.4 =formula

=A2+(B2*TIME(0,INT(C2),MOD(C2,1)*60))



If this post helps click Yes

 

[David Biddulph]

I don't think you treated 1.82 as 1 minute and 0.82 seconds; I think your
formula tries initially to treat it as 1.82 minutes, as you multiplied the
0.82 by 60 to give seconds, but by using nthe TIME function you've lost the
fractions of a second there.

The naive way would be merely to use =TIME(0,C2,0), but I guessed that you'd
done the INT and MOD split because you realised that the TIME function takes
its 3 arguments as integers, and loses the fractional part of any input, so
=TIME(0,1.82,0) gives 1 minute, not 1.82 minutes.
Unfortunately, the same discarding of fractional parts applies to the
seconds as to the minutes, so your 60*0.82 which gives 49.2 is rounded down
to 49 seconds.
Hence your =A2+(B2*TIME(0,INT(C2),MOD(C2,1)*60)) gives the same result as
=A2+(B2*TIME(0,0,C2*60)) and it is multiplying the 20 not by 109.2 seconds
(1.82 minutes), but by 109 seconds.

 

[Jacob Skaria]

Thanks David for your time in elaborating. I understand; I went wrong.....