Total Hrs calculation

[mmmarks]

Hi Team,

I have created date & Time and Resolved Date & Time.

I would like to calculate total number of minutes but trick is here to exclude Specified TIME WINDOW that s it 8:00 PM To 04:00 AM Plus needs to be excluded weekends also (Saturday, Sunday).

Created Date	Resolve Date	Total hrs
15/08/2022 19:00	16/08/2022 05:00	2

** Manual Calculation of Total Hrs : 19:00 To 20:00 = 1 Hr + 04:00 AM To 05:00 AM is 1 HR , Total Hrs is 2 Hrs

 

[jorismoerings]

Hi,

Try this:

Book1
	A	B	C	D
12	Created Date	Resolve Date	Total hrs
13	15-8-2022 19:00	16-8-2022 05:00	2	2
Sheet1

Cell Formulas
Range	Formula
D13	D13	=((NETWORKDAYS.INTL($A13,B13,"0000011")-1)*("20:00"-"04:00")+IF(NETWORKDAYS.INTL(B13,B13,"0000011"),MEDIAN(MOD(B13,1),"20:00","04:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL($A13,$A13,"0000011")*MOD($A13,1),"20:00","04:00"))*24

 

[kvsrinivasamurthy]

Try this.
Created date=N4 : Resolve date=O4 : Start Time = S3 (4:00 hrs) : End Time= T3 (20:00 hrs)
Holidays range K4:K11

=IF(AND($N4>0,$O4>0),IF(INT($N4)-INT($O4)=0,MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT($O4)-INT($N4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT($O4)-1,$K$4:$K$11)),"")

If holidays list is not there

=IF(AND($N4>0,$O4>0),IF(INT($N4)-INT($O4)=0,MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT($O4)-INT($N4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT($O4)-1)),"")