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

Hi,

The Saturday is calculated with: SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(D2)&":"&INT(D2+E2))))=7))

ROW(INDIRECT(INT(D2)&":"&INT(D2+E2))) is generating all days between start and end and tested with WEEKDAY to catch the Saturdays. SUMPRODUCT is then summing all Saturdays.

An alternative: =WORKDAY.INTL(F1,INT(J1),"0000010")+F1-INT(F1)+J1-INT(J1)

wherein "0000010" is specifying the Saturday as an off day. The range starts with Monday and ends with Sunday.

Answer 4

Thank you .... this formula worked! ... As far as how to enter the Saturdays I am still a little confused.

Answer 5

Excel 2010/2013

Similar to Bernd's

For quite advanced users.

http://www.mediafire.com/download/ay3zi6alcop7ye8/12_16_15.xlsm

http://www.mediafire.com/download/p4pjctw4jlfa4cc/12_16_15.pdf