Formula for working days

Antz1888

New Member
Joined
Feb 8, 2017
Messages
8
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
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,963
Welcome to the forum.

See if this does what you want.

Excel 2012
ABC
411/11/2015 13:4911/12/2015 8:49
58/12/2015 16:368/13/2015 11:36
69/13/2015 7:509/13/2015 10:50
710/14/2015 11:1710/14/2015 14:17
82/10/2017 16:302/13/2017 11:30

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C4=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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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

New Member
Joined
Feb 8, 2017
Messages
8
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.
 

Forum statistics

Threads
1,081,556
Messages
5,359,546
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top