| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for time.
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 :) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for time.
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 --------------- Jacob Skaria "Nicole" wrote: 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 :) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for time.
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)) -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Nicole" wrote: 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 :) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for time.
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 --------------- Jacob Skaria "David Biddulph" wrote: 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)) -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Nicole" wrote: 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 :) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for time.
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. -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "David Biddulph" wrote: 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)) -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Nicole" wrote: 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 :) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for time.
Thanks David for your time in elaborating. I understand; I went wrong.....
"David Biddulph" wrote: 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. -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "David Biddulph" wrote: 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)) -- David Biddulph "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Nicole" wrote: 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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time formula returns night time by mistake | Excel Discussion (Misc queries) | |||
| formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
| convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Linear Mode
