Calculation Ending DATE-Time knowing the duration time but following a specific hourly shedule

[ggendron]

Hi



I'm trying to calculate the End Date Time knowing the Duration (in hours:minutes decimal unit) and following a specific schedule.



Example:

Monday: Start work at 7:30 until 9:00

Monday: --Break--

Monday: Start work at 9:15 until 12:00

Monday: --Lunch--

Monday: Start work at 12:30 until 15:00

Monday: --Break--

Monday: Start work at 15:15 until 16:15



Tuesday to Thursday, is same as Monday



Friday: Start work at 7:30 until 9:00

Friday: --Break--

Friday: Start work at 9:15 until 12:00



I know the estimated work duration time to spend to accomplish the work but want a formula to calculate, when will be the End-Time according to the schedule, no working time during weekend and during holidays.



I cannot found the answer on the web.



An Excel complex formula or VBA can works.



Gabriel

 

[James006]

Hi Gabriel,

This issue does ring a bell ... (back in 2011...!!!)

Barry's excellent contribution ... see http://www.mrexcel.com/forum/excel-...-time-working-hours-duration.html#post2600736

HTH

 

[ggendron]

Hi James006

Thanks for the information. I have look at the 2011 Post and it can answer a part of my issue. It can manage a single hourly schedule for AM and another one for PM.

In my post, I have many hourly schedules AM and PM same from Monday to Tuesday but only AM on Friday.

In the End Date Time, I don't want to get an estimated End Date Time that will fall exactly during the break time of the employees. I is why, I'm looking for a solution that can handle multiple working range in AM (Beginning to work until AM Break (7h30-9h00) , Return from AM Break until Lunch (9h15-12h00), Return from Lunch until PM Break (12h30-15h00), Return from PM Break until End of the shift (15h15-16h15)). Of course, that applied from Monday to Thursday because Friday is similar to AM of Monday to Thursday but without Lunch and PM Break.

Is there a solution that can handle that without having to go with VBA ? Maybe a solution that can exclude some Hourly range in time a bit like excluding the Holidays like in the WorkDay function?

Gabriel

 

[James006]

Hi Gabriel,

From the description of all your constraints, it seems to me that, based on the Workday() function, your problem could be sliced up ...for each of your working ranges ...
which could, then, be added up ...

HTH

 

[ggendron]

Hi James

I can probably handle a small elapse time duration time calculation on a single Workday() calculation but I don't know too much how to handle a duration time that will pass through Thursday-Friday means included a 2 différents workday() calculation in a single calculation.

I really need to think about it but probably, it will end-up in vba fonction.

Regards

Gabriel

 

[James006]

Hi James

I can probably handle a small elapse time duration time calculation on a single Workday() calculation but I don't know too much how to handle a duration time that will pass through Thursday-Friday means included a 2 différents workday() calculation in a single calculation.

I really need to think about it but probably, it will end-up in vba fonction.

Regards

Gabriel

You can decide to go the UDF route or stick to a formula ...

In both cases, the underlying logic will be identical ...and your end result will be the addition of all the workday() sub-sets ...

Cheers