Calculating Time Slots

**rmhodson1** · 08-13-2018, 10:28 AM

Hi,

I have been struggling with this problem all day and I was wondering if anyone on here may be able to help me please. We are running a factory with two shifts 06:00 until 16:00 and then 20:00 until 06:00 Monday to Thursday. When I know the start date and time of a production run and I know how many hours the product will run for I want to calculate the finish time but taking into account the period each day between 16:00 and 20:00 when there is no production. If there wasn't a four hour gap each day I could do it quite easily but this four hours is causing me hours and hours of pain, please can anyone help?

Product Run Time (hrs) Start Date and Time End Date and Time
X 4 Mon 13 Aug 18 10:45 = Start Date and Time + Run Time
Y 13 = Previous End Date and Time = So on and so on.
Z 22
A 8
B 3
C 4
D 2

**modytrane** · 08-13-2018, 10:41 AM

Can you attach a sample file with some data and expected results?
That way we know what the format looks like and we can offer up correct formulas.
Calculate the result manually and enter it.

**rmhodson1** · 08-13-2018, 11:51 AM

Maybe I'm having a bad day as I don't even seem to be able to add the attachment.

**modytrane** · 08-13-2018, 02:23 PM

To attach a file, click Go Advanced below the text edit window, scroll down and click the Manage Attachments link, browse to the required file, and then push the Upload button, then Close Window to return to your post. (The "paper clip" icon for attachments is not currently working.)

**rmhodson1** · 08-13-2018, 02:27 PM

Ok thanks, I'm still struggling but here goes.

**JeteMc** · 08-16-2018, 11:26 PM

Hello rmhodson1 and Welcome to Excel Forum.
Wish that I could think of an easier way of accomplishing the goal, perhaps someone will be able to improve on this.
As modeled in the attached copy of the file G7:P7 is populated using the dates, G8:P9 is populated using shift times* and G10:P12 is populated using:

Formula:

Please Login or Register  to view this content.

*The first shift (Tuesday - Friday) is shown as running from midnight through 16:00 then the second shift from 20:00 to midnight.
The 'End Date and Time' values are populated using:

Formula:

Please Login or Register  to view this content.

There may be a way to (semi) automate the populating of dates and times if the overall method is of any use to you.
Let us know if you have any questions.

Calculating Time Slots

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Time Slots

Re: Calculating Time Slots

Re: Calculating Time Slots

Re: Calculating Time Slots

Re: Calculating Time Slots

Re: Calculating Time Slots

Thread Information

Users Browsing this Thread

Similar Threads

booking time slots

[SOLVED] Conditionl Formating to highlight time slots between start and Finish time.

[SOLVED] Calculating averages of specific time slots for each day in a given month, please hep?

Hi, I have hourly time series data and i need to convert it to 15 min time slots

[SOLVED] calculating hours using scheduled workers and time slots.

[SOLVED] calculating a time range into predefined time slots

[SOLVED] counting time slots

Bookmarks

Bookmarks

Posting Permissions