Calculate a finish time given a production rate

[Florotory]

Hi,

I am trying to create a spreadsheet that will calculate cases per hour stocked. I have tried and tried and cannot get it formulate correctly. Here's basically what I need

If an employee is required to stock 10 cases per hour they have 100 cases to stock. They start stocking at 12pm. I need it to formulate the est finish time. I used those numbers just for ease of math. I can post a picture of my spreadsheet so far if that will help. Thank you

[etaf]

rather than an image - post a spreadsheet

Stock = A2
Rate per hour = B2
Start Date/Time = C2

=C2+((A2/B2)/1440*60)

format cell to a date and time format

[Richard Buttrey]

Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. Suggestion: "Calculate a finish time given a production rate."

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Florotory]

rather than an image - post a spreadsheet

Stock = A2
Rate per hour = B2
Start Date/Time = C2

=C2+((A2/B2)/1440*60)

format cell to a date and time format

Ok let me create it real quick since I forgot to save to a thumb drive before I left work.

[etaf]

i made one,and thought I had added to the post

[Florotory]

Test picks sheet.xlsx

Hopefully this will upload correctly. Just got a new desktop and still getting used to windows 8. the blue is what I would manually key in and the yellow is what I would like it to auto populate for me. Thank you again

Edit: You spreadsheet that you sent me is basically what Im looking for. I to know how to create the formulas. That way I can customize it for each department.

[etaf]

as you will see ,i uploaded a spreadsheet - we need to know the rate/hour
i have assumed 10 and hard coded into the formula - but we could put in a different cell and use that

[Florotory]

So where the "10" is I can just change that if the cases per hour changes from dept to dept. Is that correct?
=(C4/10)/1440*60

[Florotory]

I think I got it. Well I should say I think you got it. Ill post my final spreadsheet when its done.

[etaf]

Yes, BUT I would use a cell to do that, and then you can change the cell and not worry about the forumla

you could one for the group and look it up if needed - so the group rate is also automatic

for now , for example - if you put the rate, 10 in cell A1 and then for the formula put

=(C4/$A$1)/1440*60

then as you copy down , the cell ref will not change

or on a separate sheet you could have the
Dept Rate

and lookup the value for each department
as in the attached example

[Florotory]

So basically by using the formula =(C4/$A$1)/1440*60 I can basically copy/paste and it will change the cells automatically? So for example if "Consumables A" is 100 per hour and Consumables is 50 per hour I can go to the 1st cell I do and input =(d5/$A$1)1440*60 and it would basically copy =(D5/100)/1440*60 but, just change the cell location? Hope that made sense. Sorry still learning this stuff.

[Florotory]

Ok I changed it a little bit. realized I messed up. So everything in red I would like to have it auto add together. Then put a total on the "Total" box. Then use the total for the Area to give them the completion time.

[etaf]

what ever you put in cell A1 will be use to calculate the rate - the issue will be if you have different rates per description, then you can use the lookup in my previous post

[Florotory]

I think I got what your saying. That is why I decided to group all the areas that have the same completion rate together. That way its easier to manage and setup.

[etaf]

you just need to sum up the quantity

see attached
I hard coded the 100
but you could add as previous posts

[Florotory]

Thank you for being patient and walking me through this. I think I have it. Il post my finished product.

[etaf]

OK
your welcome,

did you look at the lookup version ?

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

[Florotory]

I think I did hit the reputation Icon. I can select any post on here of yours in this thread correct? Or does it have to be your 1st post in this thread. If any will work then yes I already did it

[etaf]

any is fine - thanks

[Florotory]

picks worksheet.xlsx
Ok so here is my finished worksheet. I have a little bit of refining to do on it but, mostly done. I have varying workloads based off the difficulty of the area. So some are 100 some are 80 per hour. Any issue that you see in it? Thank you

[etaf]

looks OK , as you have hardcoded
also instead of using cell+cell+cell - you can use SUM(Cell:Cell)

=(D5+D6+D7+D8+D9)
=Sum(D5:D9)


i have also just added a table and new column to show how to use a lookup table if needed

But if you not going to be changing or adding a lot of different areas and rates - no need really