Formula for working days

[Antz1888]

Hi All

I'm hoping someone can help.

I'm trying (and failing), to make a formula which adds 2 hours to a column which includes the date and time.

Some example dates:
11/11/2015 13:49
12/08/2015 16:36
13/09/2015 07:50
14/10/2015 11:17

The formula has to take account of working days (Mon-Fri) and working hours (8.45-4.45).

This means that if a a job starts later than 14:45 the remaining time should be continued from 8.45 the next working day. Any times after working hours 16:45 will start from the next working day at 8.45.

I also have to do this for +3 hours.

Is this possible? Can anyone help?

It would be much appreicated.

Thanks

antz1888

 

[Antz1888]

Can anyone help? Have I possibly explained it wrong?

 

[Eric W]

Welcome to the forum.

See if this does what you want.

Excel 2012

	A	B	C

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]11/11/2015 13:49[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/12/2015 8:49[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8/12/2015 16:36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/13/2015 11:36[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9/13/2015 7:50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/13/2015 10:50[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10/14/2015 11:17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/14/2015 14:17[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2/10/2017 16:30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/13/2017 11:30[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF(MOD(A4,1)+TIME(3,0,0)>TIME(16,45,0),WORKDAY(A4,1)+MOD(A4,1)+TIME(8,45,0)-TIME(16,45,0),A4)+TIME(3,0,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note the difference in the date format. Also, if you have to worry about holidays as well, the WORKDAY function can handle that, if you give it a list of holidays.

 

[Antz1888]

Hi Eric W

Thanks for the help.

The formula worked to an extent, however it did not work for anything outwith the working hours (08:45 - 16:46).

For any job issued:

between 00:00 - 08:45 should be 08:45 + 2 hours (10:45) of that day if a working day, or if not the next working day.
So a job issued on Monday at 06:00 should have a estimated completion time of 10:45 (08:45 + 2 hours).
And a job issued on a Saturday or Sunday at 06:00 should have an estimated completion time of 10:45 on the Monday (08:45 + 2 hours).


between 16:45 - 00:00 should be from 08:45 the next working day + 2 hours = 10:45 next working day.
So a job issued on a Monday at 17:00 will have an estimated completion time of Tuesday 10:45 (08:45 + 2)
And a job issued on a Friday at 17:00 should have an estimated completion time of Monday 10:45 (08:45 + 2).

What did work well was that any job that was not finished by the end of the work day, the time left over from the 2 hours to be added did add on to 08:45 the next working day which is ideal.

Is there any way to fix the issues above?

What did work well was that any job that was not finished by the end of the work day, the time left over from the 2 hours to be added did add on to 08:45 the next working day which is ideal and I do not want to lose this.