Date/Time Excel Formula Calculation Help for Intermediate user in Excel 2016

[MLBrown]

Hello,

I am trying to create a Manufacturing ProductionSchedule. I am looking for help with a formula that once given start date/time,will then calculate the end date/time. The formula for the time betweenthe start date and end date, is calculated by using the Machine/CycleTime (Kept on a separate Page) divided by the available minutes in a work shiftat 90% which is 25900.
That end date/time would then be the start date/time forthe next production order, etc. Production only runs Monday-Friday and notholidays, but does run three shifts.

Any help in this would be greatly appreciated. I have been trying to figure this out for acouple of months now!

MLBrown

 

[Oaktree]

Welcome to the board.

You mentioned that you run 3 shifts, but what are the times of those? e.g. when does the first shift start on Monday (or Sunday) and when does the last shift end on Friday (or Saturday)?

Are you OK with a UDF (user defined function in VBA)? Or do you need a non-macro solution?

 

[MLBrown]

[TABLE="width: 549"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="64" style="width: 48pt;" span="10"> <tbody>[TR]
[TD="width: 92, bgcolor: transparent"]3rd Shift Hrs[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12:00 AM[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8:30 AM[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1st Shift Hrs[/TD]
[TD="bgcolor: transparent, align: right"]8:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]4:30 PM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2nd Shift Hrs[/TD]
[TD="bgcolor: transparent, align: right"]4:00 PM[/TD]
[TD="bgcolor: transparent, align: right"]12:30 AM[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 11"]Thank you! We start our work week on Sunday night at 12:00 AM and the last shift for the week ends on Saturday morning at 12:30 AM.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 9"] I am just fine with a user defined function rather than a Macro. Any help is greatly appreciated![/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Thank you![/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]