Find End Date and Time with Working Hours and Duration

[James006]

Hi,

Say Working Hours are defined as follows :
Mornings F2:G2
AfterNoons F3:G3

In cell A2, there is the Start Date and Time
In cell B2, there is a project duration expressed in hours

My Objective is to get the End Date and Time in cell C2

Thanks in advance for your help

Cheers

 

[RichardMGreen]

Assuming the two cells for each time are hours for each section, you could subtract the end from the start of each one and add them together to give the number of worked hours per day.
You would need to divide the total number of project hours by the calculation you've just performed and add that figure to the start date/time of the project.

Should give you what you need.

 

[James006]

Hi Richard,

Thanks for your input ... You are right ...

But the complication comes with the latter part , since adding hours to the Start Date has to be done within the frame of the working hours ...

Cheers

 

[James006]

Hi,

Just forgot to mention there is no need to worry about holidays ...

But indeed week-ends consisting of Saturdays and Sundays are to be skipped, in the calculation, to come up with the End Date and Time ...

Thanks in advance

Cheers

 

[barry houdini]

This is quite complex, Richard, just to check, how are the hours expressed in B2? Is 100 hours shown as just the number 100 or as a time value like 100:00?

 

[James006]

Barry,

Thanks a lot for your input ...

As we speak, hours are shown as time value 100:00 for an estimate of the total duration of the project ...

However, if it adds unnecessary complexity to the calculation, I can force the input to be a flat number ...

Thanks in advance for your insight

Cheers

 

[James006]

Hi Barry,

Is there a particular difficulty at handling the lunch break ...?

If so, I could probably convince the user to drop this idea ...

Cheers

 

[barry houdini]

OK, I split it in 2, end date in one cell (C2) and end time in another (D2)

First off, to simplify a little use this formula in H3 the get the total hours in a day

=G3-F3+G2-F2

Now in C2 for the end date

=WORKDAY(A2,CEILING(ROUND((B2+MOD(A2,1)-F$2-IF(MOD(A2,1)>G$2,F$3-G$2))/H$3,9),1)-1)

format in required date format

and in D2 for end time

=B2-(NETWORKDAYS(A2,C2)-1)*H$3+MOD(A2,1)+((B2-(NETWORKDAYS(A2,C2)-1)*H$3+MOD(A2,1)-(MOD(A2,1)>G$2)*(F$3-G$2)>G$2)-(MOD(A2,1)>G$2))*(F$3-G$2)

format as hh:mm

 

[James006]

Barry,


Many Many Many Thanks ...!!!

There is absolutely no way I could have sorted it out, on my own ...
despite injecting quite a substantial number of hours ...

Appreciate a lot your kindness ... !!! and your exceptional talent ... !!!

Again, a huge bunch of Thanks ...!!!

All the Best,

Cheers
James

 

[imaad]

Dear Barry,
hope you are well. i need to know this formula in details, what you calculate in these cells? =G3-F3+G2-F2