Logic with date in excel

Asked

Viewed 550 times

3

People, I am developing a spreadsheet to better control the production of the factory in which I work, I do not understand much of excel and I have a doubt that it may be simple to solve. Next, I have 35 machines available for production, and I know the time it takes for this machine to complete a production order with an X quantity, I have a list of orders, and I calculate the production time on the machine, and I would like to fit the date of the next request based on the deadline of the previous one: I will demonstrate:
order 1 = 6 machines (has 35 spare 26) starts day 04/06
order 2 = 37 machines (have 26 left, can start producing simultaneously), also starts day 04/06
however as the first order will end, on 05 I can take the 6 machines to give continuity in the order 2, and so on, it goes adding these dates, then I will have a production Gantt, and still in the order 2 I will have 2 production orders pending (37-35). Any idea how to do?

inserir a descrição da imagem aqui

thank you in advance!

  • Either it is poorly explained or your doubt seems to me to be more mathematical than Excel. If you divide the number of orders by the total of machines and round up, for the next integer, you will have the number of "cycles" needed for production. For example, with 50 orders and 35 machines (50/35 = 1,43 => teto(1,43) = 2) 2 cycles are required: in the first case the 35 machines produce 35 orders and in the second they produce the remaining 15 orders. So just multiply that number of cycles by the time the machines take (assuming, of course, that they all take the same time).

  • If that’s what you want, you don’t need VBA. Just calculate the number of cycles and multiply by the time in a formula.

  • Thanks for replying @Luizvieira, I was using this logic, but I have a list of orders, and in it I calculate the production time on the machine, and I would already like to fit the date of the next order based on the previous deadline: I will demonstrate: request 1 = 6 machines (has 35 spare 26) starts on 04/06 request 2 = 50 machines (has 26 left, I can start producing simultaneously), also starts on 04/06 but as the first order will end, I can take the 6 machines to continue the order 2, and so on, he keep adding up these dates, then I’ll have a production Gantt

  • Hi Denis. Well, that’s not what the question says. Why don’t you edit it and improve the text, including this example and even an image with the chart you imagine? It’ll make it easier for you to get an answer.

  • 1

    Opa @Luizvieira, alterei, agradeço a força!

  • For what you want is easier to install the msproject. But as I imagined(sya situation may be the same as mine) the company does not provide the software license. Just one question: in the colored squares you are entering some value?

Show 1 more comment

2 answers

1

What the @bp002 user said may be the only solution if you want the system to actually "count" the time - in this case, you would be developing an application using Visual Basic language. But you can round the split to have the amount of cycles that would be required for each production order using the function ceiling(). I believe that would solve your problem.

0

Yes, it is possible using vba, imagine a list of size N where you add the OPS to the machines, and a thread that looks at that list, takes a position from the list, processes, and marks with 0, hence you a global timer, and an internal one within the threads of the machines, soon you use a loop to start the threads that will read the list, from the 35, the thread needs, know which position != 0 , if you can process the position, position == 0, it has already been processed, so it should look for another position, if it does not find the thread terminates.

  • Bah n has no idea how it starts haha thanks for replying @bp002

Browser other questions tagged

You are not signed in. Login or sign up in order to post.