Combining workday function with time

Question

Would someone be able to help me out ? I have a 24hr work day and I would like to compute the end date and time excluding Saturdays. The work day would start every Sunday at 11:00PM. I plan to manually input the hours needs portion but I am unsure on how to get the correct end date and time. 

Start Date & Time	Hours Needed	End Date & Time
1/4/16 6:00 AM	52.8	?

Answer 1

Hello,

Right. That's what my UDF sbAddHours results in:

https://dl.dropboxusercontent.com/u/6077606/sbAddHours\_Example.xlsm

(Use at your own risk)

You just set up a weekly table, set Mondays to Thursdays to 00:00 - 24:00, Fridays 00:00 - 23:00 and Sundays 23:00 - 24:00 (Saturdays stay at 00:00 - 00:00), and there you are.

But please do continue to break your fingers with worksheet functions.

A Merry Christmas,

Bernd

Answer 2

Hi,

I think you can simplify your approach. As you are working 24/24 the start and end time of the working day doesn't matter, the day starts at 11:00 PM and ends at 11:00 PM, not at 10:59 PM. Whatever starting and ending we will use, the difference is always 24 hours or in Excel terms  a full day equals to 1.

=D2+E2+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(D2)&":"&INT(D2+E2))))=7))

D2+E2 will give the end time in Excel approach, otherwise saying counting hours in full days. So the only thing to include is the number of saturdays in that period.

Note: fill in the duration in hours, not as a decimal number. 72.0 is in Excel time 72 days where your intention is to say 72 hours (72:00). If that is an issue, we can convert 72.0 to hours, just let us know.

Start Date	Duration in hours	End Date
12/28/2016 06:00	72:00:00	01/01/2017 06:00
01/04/2016 06:00	52:48:00	01/06/2016 10:48

Answer 3

I tried using the suggested solution but, I am still having issues. I am getting a #NUM error in excel. The formula I used is shown below.

D2 = Start Date & Time

E2 = Duration in Hours

M2= Start of Workday ( 11:00 PM)

N2 = End of Workday ( 10:59 PM)

=WORKDAY.INTL(D2,CEILING((E2+MOD(D2,1)-M$2)/(N$2-M$2),1)*1,17)+MOD(D2,1)+E2-CEILING(MOD(D2,1)+E2-M$2,E$2-D$2)+E$2-D$2

I  used the WORKDAY.INTL function because I want to avoid Saturdays ...

Start Date	Duration in hours	End Date
12/28/16 6:00 AM	72.00	#NUM!
1/4/16 6:00 AM	52.80	#NUM!

Answer 4

I suggest to use my UDF sbAddHours:

http://sulprobil.com/Get\_it\_done/IT/Excel\_Fun/Excel\_VBA/sbCountHours/sbcounthours.html

Of course you can also try and break your fingers with a complex worksheet function approach, but even if you get it right it stays a risk whenever you or anybody else needs to amend the sheet.

Regards,

Bernd

Answer 5

A similar problem has been solved Barry Houdini earlier.

Have a look that solution (at the following URL). Hopefully it should resolve your problem.

http://www.mrexcel.com/forum/excel-questions/448073-adding-time-date-excluding-weekends-holidays.html