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

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.

Attached Files:

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

Click to expand...

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

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

Click to expand...

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:

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.
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:
and you can scroll down for the WeekEnd argument.
You can use ranges or values for the arguments.

Attached Files:

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

Click to expand...

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.

Attached Files:

Narayan, check out rows 22-26 and 34-38 of your most recent file with a 20 minute runtime.
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?

Narayan, check out rows 22-26 and 34-38 of your most recent file with a 20 minute runtime.View attachment 46604
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?

Click to expand...

Hi ,

One more mistake caught ; rectified.

Narayan

Attached Files:

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