Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
I have a spreadsheet that contains all the parts that me manufacture and the
process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
Once you have integral number of days, add that to the start date using
=WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
Sorry if I am being a little think but how do I get to the integral number of
days if the formula results in minutes? "Bob Phillips" wrote: Once you have integral number of days, add that to the start date using =WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
Say the number of minutes is in A1, then use
=INT(A1/60/24) 60 to change to hours, 24 to change to days -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... Sorry if I am being a little think but how do I get to the integral number of days if the formula results in minutes? "Bob Phillips" wrote: Once you have integral number of days, add that to the start date using =WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
BTW, the WORKDAY function is part of the Analysis Toolpak, so you will need
to check that is installed in ToolsAddins. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... Sorry if I am being a little think but how do I get to the integral number of days if the formula results in minutes? "Bob Phillips" wrote: Once you have integral number of days, add that to the start date using =WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
Thanks, I have now loaded the add-ins.
Just 1 more question (For now) If the total mins = 2400 will the INT calculate this as 1 day or 2 days? If one is ther anyway it can round it up, if it is over 1 full day to the next full day? regards "Bob Phillips" wrote: BTW, the WORKDAY function is part of the Analysis Toolpak, so you will need to check that is installed in ToolsAddins. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... Sorry if I am being a little think but how do I get to the integral number of days if the formula results in minutes? "Bob Phillips" wrote: Once you have integral number of days, add that to the start date using =WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Minutes & Hours
Ian,
INT will truncate it, so 2400 will go to 1 day. If you want to round, use =ROUND(A1/60/24,0) if you want to round up, use =ROUNDUP(A1/60/24,0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... Thanks, I have now loaded the add-ins. Just 1 more question (For now) If the total mins = 2400 will the INT calculate this as 1 day or 2 days? If one is ther anyway it can round it up, if it is over 1 full day to the next full day? regards "Bob Phillips" wrote: BTW, the WORKDAY function is part of the Analysis Toolpak, so you will need to check that is installed in ToolsAddins. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... Sorry if I am being a little think but how do I get to the integral number of days if the formula results in minutes? "Bob Phillips" wrote: Once you have integral number of days, add that to the start date using =WORKDAY(start_date,num_days) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TheRook" wrote in message ... I have a spreadsheet that contains all the parts that me manufacture and the process times for each operation to make these parts. The process times are in minutes per piece. What I am wanting to do is calulate the start day for each operation by deducting the process times from an end date (excluding non working days, we currently work 24hs 5 days per week). For example if we are making 100 of part no. 123ABC it would caculate as follows: Process times: Operation 1 = 5 mins Operation 2 = 10 mins Operation 3 = 15 mins Operation 4 = 60mins End date = 31/10/06 Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06 Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06 Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06 Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06 I currently have a spreadsheet set up that calculates starts dates for a differnt department but the process time are in days so it is quite easy. If this is difficult to express in words I can forward my email address so the you can send me an example that I can manipulate. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Hours & Minutes | Excel Discussion (Misc queries) | |||
Adding minutes to hours/minutes | Excel Discussion (Misc queries) | |||
Adding hours and minutes | Excel Programming | |||
Adding hours and minutes | Excel Discussion (Misc queries) | |||
adding hours and minutes | New Users to Excel |