network days formula not handling cases when start date is weekend

Hi Experts,

I have a formula which calculates the working hours between two dates, considering a specific working schedule and excluding weekends and holidays.

I have a problem regarding when the start date is in the weekend, example attached.

Can someone please help?

I started the formula from the scratch and ended with such construction:

Formula:

=MAX(0,MIN(FLOOR.MATH(A2)+TIME(16,0,0),B2)-WORKDAY(A2-1,1,$R$2:$R$100)-MAX(A2-FLOOR.MATH(A2),TIME(8,0,0)))+

MAX(0,NETWORKDAYS(WORKDAY(A2,1,$R$2:$R$100),WORKDAY(B2,-1,$R$2:$R$100),$R$2:$R$100))*TIME(8,0,0)+

IF(FLOOR.MATH(A2)=FLOOR.MATH(B2),0,MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0)))

it consists of three rows (three parts):
- first is for time in starting day (is 0 if it's non-working day or time after 16:00)
- second is the number of working days in between day after first day and a day before last day, multiplied at the end by 8 hours per day:
two subcomments here:
a) *TIME(8,0,0) it is equivalent to end hour minus start hour: *(TIME(16,0,0)-TIME(8,0,0)) - worth noticing if starting and ending hours are written in some cells).
b) situation of no such days is handled by using MAX(0,dates difference)
- third is the number of hours on last day, but only if last day is different than first day - IF used. It could be also written as (FLOOR.MATH(A2)<>FLOOR.MATH(B2))*(MAX(0,MIN(FLOOR.MATH(B2)+TIME(16,0,0),B2)-WORKDAY(B2-1,1,$R$2:$R$100)-TIME(8,0,0))) (I originally wrote it that way, but IF is probably easier readable).

Just to analyze (and correct? if needed) I left columns F/N(P) with partial results I used while creating the formula. To show they are not needed any longer I removed them in rows 4:9
Delete all them from final version.

Pl see file.
In C2

Formula:

=IF(AND($A2>0,$B2>0),IF(INT($B2)=INT($A2),IF(NETWORKDAYS($A2,$A2,$M$2:$M$3)>0,(WEEKDAY($A2,2)<6)*(MEDIAN($F$2,$G$2,ROUND(MOD($B2,1),6))-MEDIAN($F$2,$G$2,ROUND(MOD($A2,1),6))),0),(NETWORKDAYS($A2,$A2,$M$2:$M$3)>0)*($G$2-MAX($F$2,ROUND(MOD($A2,1),6)))+(NETWORKDAYS($B2,$B2,$M$2:$M$3)>0)*(MIN(ROUND(MOD($B2,1),6),$G$2)-$F$2)+IF((INT($B2)-INT($A2))>1,NETWORKDAYS($A2+1,$B2-1)*($G$2-$F$2),0)),"")

@kvsrinivasamurthy:
I don't think your formula is right.

Check for instance last dates used in my example in previous post.

Or looking at coming days, look for instance on:
10.05.2024 07:48 13.05.2024 07:09

It's a whole day in Friday then Sat free, Sun free and on Mon finish before 8:00 so no hours worked.
But your formula returns (for end of working day at 18:00, so 10 hrs/day) 9:09:11, not full 10 hrs worked on Friday.

To make debugging easier (probably) check also pairs:
10.05.2024 07:48 09.05.2024 07:09 (shall be 0 as work was finished before start, but result of your formula is again 9:09:11)
and pair 11.05.2024 07:48 10.05.2024 07:09 - here you obtain negative result, which is displayed as ######

If we think that such data pairs could be entered into worksheet, may be formula shall be nested in =IF(B2<=A2,"Please correct dates finish shall be later than start", ... current formula ...)



I like the idea to add checking if both A2 and B2 are >0 so no result is displayed until both dates are entered into worksheet.


Having this in mind and replacing FLOOR.MATH with INT function in my formula, and adjusting holidays to range you used in your file we end up with:

Formula:

=IF(AND(A2>0,B2>0),

MAX(0,MIN(INT(A2)+G$2,B2)-WORKDAY(A2-1,1,M$2:M$3)-MAX(A2-INT(A2),F$2))+

MAX(0,NETWORKDAYS(WORKDAY(A2,1,M$2:M$3),WORKDAY(B2,-1,M$2:M$3),M$2:M$3))*(G$2-F$2)+

IF(INT(A2)=INT(B2),0,MAX(0,MIN(INT(B2)+G$2,B2)-WORKDAY(B2-1,1,M$2:M$3)-F$2)),

"")

Try in C2

Formula:

=IF(AND($A2>0,$B2>0),IF(INT($B2)=INT($A2),IF(NETWORKDAYS($A2,$A2,$M$2:$M$3)>0,(WEEKDAY($A2,2)<6)*(MEDIAN($F$2,$G$2,ROUND(MOD($B2,1),6))-MEDIAN($F$2,$G$2,ROUND(MOD($A2,1),6))),0),(NETWORKDAYS($A2,$A2,$M$2:$M$3)>0)*($G$2-MEDIAN($F$2,$G$2,ROUND(MOD($A2,1),6)))+(NETWORKDAYS($B2,$B2,$M$2:$M$3)>0)*(MEDIAN(ROUND(MOD($B2,1),6),$F$2,$G$2)-$F$2)+IF((INT($B2)-INT($A2))>1,NETWORKDAYS($A2+1,$B2-1)*($G$2-$F$2),0)),"")

guys, thank you for all your help. incredible display of skills. adding rep & marking this as solved.