Creating a timebound plan for a 17 hour day

1. 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

2. Re: Creating a timebound plan for a 17 hour day

Hi

Product start at
A1=[29-10-2015 06:00] and run for
B1=[5294] it ends at =A1+B1/60/17 [03-11-2015 10:33]
use this
Formula:
`Please Login or Register  to view this content.`

I hope this helps

3. Re: Creating a timebound plan for a 17 hour day

Hi thanks for the response,

i Believe this will just take an average time. Therefore its still possible that the finish time could be after 23:00 and before 06:00.

I'm pretty sure it needs to be an IF statement, possible nested.

I can easily make this work for a one day period but as soon as the product duration is greater than 48 hours it throws an error. maybe if could upload the file for you to have a visual?

many thanks

4. Re: Creating a timebound plan for a 17 hour day

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.

5. Re: Creating a timebound plan for a 17 hour day

Trial Packing Scheduler Ver2.xlsx

Not sure if its attached or not. let me know.

Yes, it would need to finish at 9am.

I hope you can help me!

thanks

6. Re: Creating a timebound plan for a 17 hour day

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.

7. Re: Creating a timebound plan for a 17 hour day

If you dont want weekends, can the start date + total minutes ever span over 2 weekends or never that long

8. Re: Creating a timebound plan for a 17 hour day

Originally Posted by garfield101
If you dont want weekends, can the start date + total minutes ever span over 2 weekends or never that long
It's unlikely but it's still possible.

My main issue is with the time. The IF statement avoids the first 23:00 > 06:00 occurrence but if the product then runs through the whole 17 hour day again, then IF statement doesn't pick up the second day.

If you download that example hopefully you will be able to see what im trying to achieve.

many thanks

9. Re: Creating a timebound plan for a 17 hour day

This solution might work just for the time. I know the date doesnt work for all cases.

10. Re: Creating a timebound plan for a 17 hour day

Scheduler ver3.xlsx
Originally Posted by garfield101

This solution might work just for the time. I know the date doesnt work for all cases.
Yes, that works for the time for one row only. Looks as if the start time cell has been locked to row 5. this plan is ongoing and would need to use the previous finish time as the new start time.

Perhaps you could rearrange what you've produced so it runs continuously?

Absolutely great progress so far!

11. Re: Creating a timebound plan for a 17 hour day

See if this is better

12. Re: Creating a timebound plan for a 17 hour day

Originally Posted by garfield101
See if this is better
Scheduler ver3.1.xlsx

That's exactly what I was asking for. That's amazing help!

So, to address the date issue? How do you go about that? Making it add the hours to make it the correct date but avoid weekends?

13. Re: Creating a timebound plan for a 17 hour day

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

14. Re: Creating a timebound plan for a 17 hour day

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)

15. Re: Creating a timebound plan for a 17 hour day

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.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1