Using Business Hours to find the Expected Finish Time

[Adam410]

Hi there,

I am working on an Excel based production schedule for the company I work for. We can have production runs that last for a couple of hours right up to a week.

We have an average run rate that we can use to determine the time, but I am struggling to find an equation for the finish time as we are not a 24 hour business.



As our shop floor works from 7 till 4, the end time of the second run will actually be 06/01/2017 7:47 AM.

Does anyone have any idea how I can do this?

Many thanks!

 

[Chihiro]

Need more info.

1. Do we need to consider weekends/holidays?
2. What format is Run Time in? dd:hh:mm? hh:mm:ss?

Can you upload sample workbook with exact format and few examples where you have trouble.

 

[Adam410]

Hi Chihiro,

1. Yes, weekends and holidays will apply (Friday working hours are actually 7 till 1 as well)
2. Run time should be hh:mm:ss (my mistake, I apologise. formatting isn't important, whatever is easiest)

I was working on the following formula, but this only works from the start of the day:

=WORKDAY($A2,INT($B2/7.5))+MOD($A2,1)+MOD($B2,7.5)/24

 

[Chihiro]

Ok, that makes it bit more complicated. I'm going into meeting at work and will be tied up.

If someone else doesn't help you before. I'll take a look at it tonight.

 

[Chihiro]

Hmm, are you ok with UDF solution?

 

[Adam410]

Absolutely!

 

[Chihiro]

Here, test this out.

I haven't added functionality for Holidays yet.

Note that work hours for each day of week and start time for each day of week is filled in "fillDictionary" sub and you can edit this to modify start time and work hours if it changes.

If you can verify validity of this function. I'll add portion for holidays.

 

[Adam410]

Here, test this out.

I haven't added functionality for Holidays yet.

Note that work hours for each day of week and start time for each day of week is filled in "fillDictionary" sub and you can edit this to modify start time and work hours if it changes.

If you can verify validity of this function. I'll add portion for holidays.

It is working perfectly up until weekends! Thanks!

 

[Chihiro]

Here you go. In sheet2 added named range "Holidays".

It's only set for 2 rows now for testing. Extend as needed. Last portion of code modified.

 

[Adam410]

still doesn't seem to work for weekends?

 

[Chihiro]

Can you elaborate? Since your office is closed on weekends, it should count out weekends right?

Can you upload sample with what UDF returns and what is expected?

 

[Adam410]

Can you elaborate? Since your office is closed on weekends, it should count out weekends right?

Can you upload sample with what UDF returns and what is expected?

From what I can see, it is going into Saturday/Sunday rather than onto Monday? I will upload a file asap.