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

  • 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

Hans Öjes

New Member
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.
 

Attachments

  • Example.xlsx
    9.4 KB · Views: 10
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
 

Attachments

  • Book 1.xlsx
    10.1 KB · Views: 9
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

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
 

Attachments

  • Backward planning 20171018_01.xlsx
    55.8 KB · Views: 4
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
 
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
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.
 

Attachments

  • Backward planning 20171018_02.xlsx
    62 KB · Views: 5
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
To reply to your question 2: Yes, that would work fine
 
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
 
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.
 

Attachments

  • Chandoo36124Backward Planning.xlsm
    52.4 KB · Views: 7
Last edited:
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 other's results differ from mine.
View attachment 46583
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
 
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'.
 
Hi ,

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

Narayan
 

Attachments

  • Backward Planning.xlsx
    44.1 KB · Views: 8
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?
 
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
 
Back
Top