+ Reply to Thread
Results 1 to 15 of 15

Creating a timebound plan for a 17 hour day

  1. #1
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    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
    Last edited by LukeGilfoyle; 10-30-2015 at 05:58 AM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    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: copy to clipboard
    Please Login or Register  to view this content.

    I hope this helps

  3. #3
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    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. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    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.
    ChemistB
    My 2˘

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    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. #6
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    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.
    Last edited by LukeGilfoyle; 10-29-2015 at 05:48 AM.

  7. #7
    Registered User
    Join Date
    10-10-2015
    Location
    Houston
    MS-Off Ver
    2013
    Posts
    26

    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. #8
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Creating a timebound plan for a 17 hour day

    Quote Originally Posted by garfield101 View Post
    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. #9
    Registered User
    Join Date
    10-10-2015
    Location
    Houston
    MS-Off Ver
    2013
    Posts
    26

    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Creating a timebound plan for a 17 hour day

    Scheduler ver3.xlsx
    Quote Originally Posted by garfield101 View Post

    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. #11
    Registered User
    Join Date
    10-10-2015
    Location
    Houston
    MS-Off Ver
    2013
    Posts
    26

    Re: Creating a timebound plan for a 17 hour day

    See if this is better
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Creating a timebound plan for a 17 hour day

    Quote Originally Posted by garfield101 View Post
    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. #13
    Registered User
    Join Date
    10-10-2015
    Location
    Houston
    MS-Off Ver
    2013
    Posts
    26

    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
    Attached Files Attached Files

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    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)
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Creating a timebound plan for a 17 hour day

    Quote Originally Posted by garfield101 View Post
    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.
    Attached Files Attached Files
    Last edited by LukeGilfoyle; 10-30-2015 at 05:14 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 03-26-2014, 05:23 AM
  2. MLM matrix plan plan 4x4 coding/formula
    By ncsvapi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2013, 08:23 AM
  3. creating a staff rota for 24 hour shifts
    By RONJONES in forum Excel General
    Replies: 4
    Last Post: 09-06-2013, 12:02 PM
  4. Replies: 3
    Last Post: 08-25-2011, 05:52 AM
  5. Replies: 0
    Last Post: 01-14-2009, 09:26 PM
  6. Creating an average on a day-hour-minute formula
    By KcNewb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-24-2008, 04:44 PM
  7. creating a 2d plan graphic and projecting points in it
    By seether in forum Excel General
    Replies: 2
    Last Post: 05-17-2008, 06:24 AM

Tags for this Thread

Bookmarks

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