1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Backwards planning with a schedule

Discussion in 'Ask an Excel Question' started by Hans Öjes, Oct 17, 2017.

  1. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    I am trying to plan backwards based on the followingas input:
    - Planned finish time (yy-mm-dd hh:mm)
    - Runtime (hh:mm) [Time it takes to manufacture the widget]
    - Shop opening time (hh:mm)
    - Shop closing hours (hh:mm)

    What I am looking for is a formula that can calculate backwards to a planned starting time based on the above, and also take weekend restrictions into consideration (using "NETWORKDAYS")

    I hope anyone can help.
    I consider myself as being good at Excel, but when it comes to the time-functions I get stuck...

    I have attached a file with those fields with sample data, and also a "manual" calculation of what the answer would be.

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See the attached file ; I have used helper cells liberally , so that you can verify the logic for yourself.

    Try it out with as much data as possible.

    Narayan

    Attached Files:

  3. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    Thanks. I have tested and the formula crashes when the finishtime is earlier the the opening hours of the shop. I have extended the fomulas to a table with different starting time over a week with 2 hours incremental steps, and done a gant-chart to grafically graps what is happening. Please have a look, and see if you can do something about the error. I have also realized that I need to be able to input run time larger then 23:59. Do I have to use a decimal field for input?
    Check attached file

    Attached Files:

  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Please clarify / confirm the following two points :

    1. Are the calculations and the outputs correct for all times which are within your shop opening and closing times ?

    2. If a planned finish time is before shop opening time , can we take it as the same as the shop closing time the previous day ? This will be easy to incorporate into the existing calculations.

    Narayan
  5. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    Hi. I looked further into checking the calculations. I have made "Manual" calculattions of the total time used for different cases (In rows BF_BU)
    The first 5 works correct, the I skipped those with the previously detected error, and then there are miscalculations again. Please check and see if you can find what is causing it.

    Attached Files:

  6. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    To reply to your question 2: Yes, that would work fine
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    It is late tonight for me , and I will be able to post a revised file only tomorrow.

    It is possible someone else may post their solution before that.

    Narayan
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See if this works correctly.

    Narayan

    Attached Files:

  9. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    It seems to be working just fine!
    You are a star!!
    Now the last thing. I need to be able to enter a runtime > 23 hours. Is that possible to fix?
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    The attached file can cater to a maximum run time of 40 hours.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  11. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    I can fix that by extending the number of columns, looks great. thanks a lot!
    NARAYANK991 likes this.
  12. vletm

    vletm Well-Known Member

    Messages:
    2,854
    Hans Öjes
    ... based You original file;
    Could Your Backwards planning with a schedule be like this?
    (Shop opens/-closes, Planned finish time, runtime and Calculated start time)

    Attached Files:

    Thomas Kuriakose and sathishsusa like this.
  13. p45cal

    p45cal Well-Known Member

    Messages:
    884
    The attached is a comparison of 3 methods:Narayan's,vletm's and mine.
    Open/Close times are 7am and 16:45pm in all cases.
    Narayan's and mine agree on all dates except 3, where Narayan's suggests starting work on a Sunday (am I right that Sat and Sun is the weekend?)
    Vletm's disagrees more often (I hope I've used the function correctly?)
    The orange shading is where others' results differ from mine.
    upload_2017-10-20_0-59-39.png
    Mine can include holidays and allows a change to what the weekend days are according to the same argument used in the built-in Workday.INTL
    I don't think mine has a limit on the length of the run time.
    I've tested a fair amount (including holidays) but it will need more thorough checking, and I haven't tested at all for a change in what the weekend days are.

    I haven't tried to streamline my UDF yet, it's just as it was when I finally got it to work! There were some interesting/aggravating anomalies trying to get the application.worksheetfunction.workday_intl to act as expected (try it with -0 for the number of days…)

    Use as follows:
    upload_2017-10-20_0-57-48.png
    and you can scroll down for the WeekEnd argument.
    You can use ranges or values for the arguments.

    Attached Files:

    Last edited: Oct 20, 2017
    Thomas Kuriakose and NARAYANK991 like this.
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Thanks for testing thoroughly.

    I'll look into the problem ; hopefully since the logic is quite transparent , it should be possible to fix it easily.

    Narayan
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See the revised file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  16. vletm

    vletm Well-Known Member

    Messages:
    2,854
    p45cal
    You have tested 'Planned finish times' mostly out of 'shop times'.
    For me ... runtime ... would 'use' only within 'shop times'.
    I've tested only that those times would be within 'shop times'.
  17. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    Thanks to all 3 of you: Narayan, P45cal & Vletm
  18. p45cal

    p45cal Well-Known Member

    Messages:
    884
    That is true.:)
  19. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    I entered a low runtime in Narayan's model (00:20), and it calculated the start time as being > finish time, so something is still wrong there
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Thanks for the stress testing ; let me check it out.

    Narayan
  21. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    I did some testing of my own , and I have corrected another mistake in my earlier upload. Check this file.

    Narayan

    Attached Files:

  23. p45cal

    p45cal Well-Known Member

    Messages:
    884
    Narayan, check out rows 22-26 and 34-38 of your most recent file with a 20 minute runtime.
    upload_2017-10-20_14-13-57.png
    A finish of 7:40am on a Saturday means the run should have finished at 16:45 on Friday. 20 minutes before that is 16:25 , rather than 07:20, no?
  24. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    One more mistake caught ; rectified.

    Narayan

    Attached Files:

    sathishsusa and Thomas Kuriakose like this.
  25. Hans Öjes

    Hans Öjes New Member

    Messages:
    12
    Now I have tested Narayan against p45cal for a complete week, using run times 00:20, 09:30, 55:25 and both methods shows the same results. (Vietm's solution looped for me at 00:20 so I had to remove it from the comparsion). I will work with the data during the weekend, and hopefully we are done now. If I find something else odd, I'll post it. thanks again
    NARAYANK991 likes this.

Share This Page