Adding hours to datetime within varied working hours + past midnight

[Samanar]

Hello everyone,

My first post here, I've searched the forums extensively for a past week but couldn't find exactly what I'm working on. Unfortunately my case has some weird details

.

I'd like to ask you for help as I'm stuck right now. Here's the table showing what I mean:

[TABLE="width: 500"]
<tbody>[TR]
[TD]start =now()[/TD]
[TD]start shift[/TD]
[TD]end shift[/TD]
[TD]hours to add[/TD]
[TD]expected result[/TD]
[/TR]
[TR]
[TD]2-24-17 17:00 PM[/TD]
[TD]0:00[/TD]
[TD]10:00[/TD]
[TD]8[/TD]
[TD]2-27-27 08:00 AM[/TD]
[/TR]
[TR]
[TD]2-24-17 17:00 PM[/TD]
[TD]9:00[/TD]
[TD]17:00[/TD]
[TD]12[/TD]
[TD]2-28-17 12:00 PM[/TD]
[/TR]
[TR]
[TD]2-24-17 17:00 PM[/TD]
[TD]23:00[/TD]
[TD]7:00[/TD]
[TD]3[/TD]
[TD]2-27-17 02:00 AM[/TD]
[/TR]
</tbody>[/TABLE]

In short: I'd like to have possibility to prepare this separately for several different (fixed) working hours and also for different weekdays (some teams would work Sunday-Thursday in example instead of MON-FRI). Number of added hours would be user input.

Examples:

I open the the task which takes 8 hours on Friday 2/24/17 17:00

• I work MON-FRI 11:00 - 19:00 - the correct result would be 2/27/17 17:00 (weekend excluded - so Monday).
• I work MON-SAT 11:30 AM - 7:30 AM (through the midnight) - the result would be 2/25/17 1:00 AM.

PS. The solution might be in VBA or Excel formula, doesn't really matter - I'm simply struggling with getting correct result.

Thank you very much, basically any help would be much appreciated!
Sam

 

[James006]

Hello,

This problem is quite complex ... :wink:

Would encourage you to look at Barry's brilliant solution ...

https://www.mrexcel.com/forum/excel...-time-working-hours-duration.html#post2602733

Hope this will help

 

[Samanar]

Would encourage you to look at Barry's brilliant solution ...

https://www.mrexcel.com/forum/excel...-time-working-hours-duration.html#post2602733

James006, this is fantastic - thank you so much. If I get longer shift which goes through midnight I can divide hours till midnight as 1st shift in F2-G2 and after midnight in F3-G3 and it... just works. I'd never work this out myself, this is brilliant!

One last detail - I still need to somehow change the weekends (not every team works MON-FRI). I know that the

[COLOR=#333333][FONT=Menlo]WORKDAY.INTL( start_date, days, [weekend], [holidays] )[/FONT][/COLOR]

formula does that but it got pretty complicated, I'd like to change it to weekends on Friday/Saturday (seems like it's value 7), any idea how to input it into the below?

=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)

Many thanks!
Sam

 

[James006]

Hello again...

You are welcome ... :wink:

As far as adjusting your formula with the argument 7 (which is optional ... like holidays...)

=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,7)

HTH

 

[Samanar]

=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,7)

Hi,

I was trying to use the above but it didn't change the weekend days. When I switched to =WORKDAY.INTL (leaving everything else like you stated) I got #NAME? error in C3 (end date) and D3 (end time) cells.

Appreciate your help!
Sam

 

[James006]

Sorry I meant WORKDAY.INTL ...

However, you need to check if function exists ...since, I think ..., it only applies to Excel 2013 and Excel 2016 ...

 

[Samanar]

Oh, that's true - I'm running Excel 2007 so this function is missing. Is there any workaround to exclude particularly Friday and Saturday as weekend using regular WORKDAY function?

 

[James006]

Oh, that's true - I'm running Excel 2007 so this function is missing. Is there any workaround to exclude particularly Friday and Saturday as weekend using regular WORKDAY function?

The workaround I can think of is the function written by Chip ... :wink:

here is the link : A Better NETWORKDAYS

Hope this will help