Creating a timebound plan for a 17 hour day

Hi there, i'm new to this site!

Fairly good with excel but have hit a problem.

I have an ongoing production plan that works on a 17 hour day basis.

It uses standards to retrieve how many minutes the product will run for (in minutes)

So, if a product starts at 06:00 and the standards say the product will run for 5294 minutes (88 hours), I want the finish time and date to add the duration to give me the new time/date.

However, I say 17 hour day because we start work at 06:00am and finish at 23:00pm.. so that means it needs to know how many days to add to the date and also needs to avoid putting the finish time after 23:00 and before 06:00. (we can't finish an order if we re at home..)

I really hope someone can help me. If you're unsure of what im asking, then I can try to explain a bit better.

thanks

Yes, upload the file (Go Advanced>Manage Attachments). So if it starts at 6AM and runs for 20 hours, does it finish at 6AM (since it really finished when no one was there) or does it finish at 9AM?

If it's the second, then I believe Jose's formula will work for you.

So basically I just need formula(s) that will:

--add the duration onto the start date/time
--if the finish time is greater than 23:00 and less than 06:00 then add the remaining time onto 06:00
--if the finish day is on a Saturday or Sunday then miss out sat and sun and put the finish day only on work days (mon-fri)

It's a processing plan than shows dates and times of when products need to be finished in a large factory.
We currently have a plan that works but is based on 24 hours so the times are always incorrect and have to manually amended.

Help would be really appreciated.

I use WORKDAY(start_date, #days) to calculate finish date
Normally, #days = #minute/60/24
This case, #days = #minute/60/17= #minute/1020

In Q5, finish date :

Please Login or Register  to view this content.

Format as dd/mm/yyyy

In P5, finish time:
=MOD(Q5,1)

Originally Posted by garfield101

With the Finish Date. I am sure there are less ugly ways of doing it. I tested for a lot of different cases, but you should also try to break it b/f using it

Hi again.

Yes, everything works fine, if the date throws an error then we can just update the cell manually.

That's such a massive help and if I need any more are you available to help?

many thanks.