Excel Production Schedule and Time Forecasting

I sort of get what you want. It would become a lot clearer with some actual data to play with. Below are the instructions and recommendations for attaching a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi Kozbot and welcome back.

The issue is we do not run 24/7. We have 3 machines, 2 run 6:00 AM-5:15 PM and another 6:00 AM -8:15 PM. If a production run takes more then 12 or 15 hours then run starts back up at 6:00 AM next day. I'd like the calculation to start back up the next day.

Some of the proposed dates (sheet 'AFTER') include weekends, but ........

Some of my date calculations may be wrong, apologize

What is the workweek (M-F ?) and do we need to account for holidays?

I thought this was what you were going to want. Fortunately I developed a UDF for this. It's called WorkHours and it's similar to the Excel Function WorkDays. The syntax is =WorkHours(Start Time, Number of Hours, Shift Start Time, Shift End Time, [Holidays]).

Holidays is a range of cells containing the dates of holidays and it is an optional argument.. The function takes into account weekends and holidays.

This could get complicated really fast. I could add a flag to tell it not to count weekends. Do I then automatically also not count holidays? What if you wanted to work the Saturday of a holiday weekend? Also if you work on Saturday, does that mean that you also work on Sunday? Also are the weekend shifts the same length as the weekday shifts. This could possibly be done, but it would take a dozen or more parameters passed to the function to do it and quite a bit of coding.

So the answer is, I think it's possible, but I'm not going to take the time to do it. The best I think I can offer is a flag to ignore both weekends and holidays and assume that the weekend shifts are the same as the weekday shifts.

Yes, the code can be copied in anywhere. It's a "stand alone" function.

Attached is a zip file. Uncompress it and the file ModWorkHouse.bas is contained within. Save the BAS file to a convenient place. Then when you need it in a workbook, open the vb editor (Alt-F11) and click on File -> Import and navigate to where you have the bas file stored. Click on it and it and the module will be imported. Excel should remember the last place you imported from, so navigation should be simpler after the first time.

Thank you!

I imported the VBA code and am encountering a problem when it calculates the result in the next day. The formula functions fine for adding hours within the day. But when the result goes into the next day I get a VALUE error.

I did not encounter this with the example workbook

Any insight?

Give me the parameters you are passing to the function and I'll see if I can reproduce the error.

I've seen this behavior before. If you have a failed macro anywhere in the workbook, then functions will return #Value. That might not be the only cause of this error, but it's one of them.

Yes, a failed macro should show up on the list.

I just thought of something else. Make sure you are passing good parameters to the function. In particular pay attention to shift start time and shift end time. Make sure these are actually times instead of date-times with just the time part showing.

Unless you are in a position to post the actual book, I don't think I can help further. Change all the names to something nominal and maybe it would be safe to post.