Backwards planning with a schedule

[vletm]

Hans Öjes
hmm ... looped for You at 00:20 ... hmm?
Not here, so far!
Could You give some values which makes that?
I could test here too.

Which range of Planned finish times do You use?
This works then Planned finish time is between 'shop opens - shop closes'.
For me, if Planned finish time is after shop closes,
then that time should write by user
to next possible working day at shop opens time ...
as well as if it should be before open time then
it should write by user as previous working day's shop's close time.
(This is same file, only shop closes time has changed to 16:45 and also I changed runtimes as You wrote.)

 

[p45cal]

Granted this is a planned finish time outside working hours but this loops indefinitely:

with 07:00 - 16:45.
It's quite a punishment for the user if he gets it wrong!
I had to force Excel to close.

 

[vletm]

p45cal
Okay ... if user do something against 'rules' then it won't be users mistake!
even there has been many times texts as
This works then Planned finish time is between 'shop opens - shop closes'.
That and even few more same kind of cases ex Planned finish time cannot be earlier than present time should be include Your 'It's quite...' or how?
That would make more challenges while using any code in the real world!

As You wrote: 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…)
Neither I haven't made that code ready ...
as give 'note' of what values would be valid ...
as which are those 'weekends' ... holidays or any other 'non working days'.

But nice to know, which make 'loop'!
... if uses that wrong way!
Thank You.

 

[vletm]

Quick Modified 'Loop Case by User',
if user won't follow instructions nor even some cases of mistakes.

 

[p45cal]

Okay ... if user do something against 'rules' then it won't be users mistake!
even there has been many times texts as
This works then Planned finish time is between 'shop opens - shop closes'.

I'm walking down the street, passing a fruit shop and see some apples on display outside the shop, I take one, without paying for it, and eat it.
If I'm caught, I expect to be punished, but not with a death sentence.

 

[vletm]

p45cal
Okay, I see ...
You know that it is totally wrong, but still You want to do it!
hmm...
You also waited that someone would notice and You would be punished
... and You 'hope' that others would understand that and give just a soft bunch
Do You know,
there are many people who don't just give a soft bunch!
Maybe those also know that it's wrong, but those don't care!

 

[Hans Öjes]

I'm walking down the street, passing a fruit shop and see some apples on display outside the shop, I take one, without paying for it, and eat it.
If I'm caught, I expect to be punished, but not with a death sentence.

Hi. I found a missmatch between P45cal & Narayan's model
Finish 2017-11-04 09:00
runtime 19:30
open 07:00
close 16:45

Result:
Narayan 2017-11-01 16:45
P45cal 2017-11-02 16:45

They are 1 day apart at this specific scnenario. I believe P45cal's is miscalculating this time

 

[vletm]

Hans Öjes
Just 'nice to know' ...
Your Planned finishtime(date) is Saturday ...
What kind of functionality have 'shop open - closes' times?

 

[p45cal]

p45cal
Okay, I see ...

You are right vletm. What a fool I've been!

(talking of 'the real world', I wonder how many copies of Excel Microsoft would have sold if, instead of displaying #VALUE! in each cell when someone entered a formula by not following the function rules (or where they adjusted a precedent cell to cause the formula to go wrong), it caused Excel to hang, and forced them to force it to close by using the Task Manager)

 

[Hans Öjes]

Hans Öjes
Just 'nice to know' ...
Your Planned finishtime(date) is Saturday ...
What kind of functionality have 'shop open - closes' times?

Sorry, missed that. My misstake, it works fine within shop hours

 

[Hans Öjes]

Hi. I found a missmatch between P45cal & Narayan's model
Finish 2017-11-04 09:00
runtime 19:30
open 07:00
close 16:45

Result:
Narayan 2017-11-01 16:45
P45cal 2017-11-02 16:45

They are 1 day apart at this specific scnenario. I believe P45cal's is miscalculating this time

Sorry, I missed that this finishtime was at a weekend. My misstake, it works fine within shop hours

 

[vletm]

Hans Öjes
One possible to try to avoid those 'weekends',
then weekends are Saturday and Sunday
and also those 'non-shop-hours'.

 

[p45cal]

The attached for testing.
Problems seem to have been encountered where exact multiples of 9hrs 45mins (the length of a working day) were used as the runtime, and where the planned finish was at the end of the working day.
Tweaks have been made to the UDF:
1. A sticking plaster botch for being a day out in some circumstances (msg#32). I will look into making this better when I get my head round the reason.
2. When a result suggested a start at the very close of play, this has been advanced to the start of the next business day.


Again, I've not streamlined this, but will wait to hear it's good enough, and that you want to make use of it. (I'll still try to get a more robust way of dealing with tweak no.1)