Net work Hours Calculation

[Keyan_rrr]

Hi, i have a similar question and mentioned below

Start date in F2 Column 5/13/2024 3:36:32 AM
End date in G2 Column 5/15/2024 10:38:40 AM

from start & end date time calculate the working hours from start shift day is 8pm in AX3 column to End shift day is 7am AY3 Column by excluding the weekend and holiday range AU2:AU12 need a excel formula. Thanks

Net work hours.xlsx
	F	G																W																							AT	AU	AV	AW	AX	AY
1	Start Date	End Date																Output in Hours																								Holiday Date			Shift Start Time	Shift End Time
2	05/06/2024 19:00	05/07/2024 19:00																2																								05/01/2024			20:00:00	7:00:00
3
4
Net Work Hours

Cell Formulas
Range	Formula
W2	W2	=NETWORKDAYS.INTL(F2,G2,1,Holidays)-(NETWORKDAYS.INTL(F2,F2,1,Holidays)*IF(MOD(F2,1)>ET,1,(MAX(ST,MOD(F2,1))-ST)/(ET-ST))-(NETWORKDAYS.INTL(G2,G2,1,Holidays)*IF(MOD(G2,1)<ST,1,(ET-MIN(ET,MOD(G2,1)))/(ET-ST))))*(ET-ST)*24

Named Ranges
Name	Refers To	Cells
ET	='Net Work Hours'!$AY$2	W2
Holidays	='Net Work Hours'!$AU$2:$AU$12	W2
ST	='Net Work Hours'!$AX$2	W2

 

[awoohaw]

Assuming your weekends are Sat and Sun. What happens on the Friday night shift? Does the shift End at 11:59:59 on Saturday, only 4 hrs of work?
How are holidays treated? Does the Monday Shift start at 00:00:00 on Monday or 20:00:00 on Sunday?

 

[Keyan_rrr]

Example:

Day 1 if the user logged at 8pm and they logged off at 7am next day then the work hour is 11
Day 2 if the user logged at 5pm and they logged off at 8am next day then the work hour is 11
Since the shift hours will start at 8pm and end in 7am in morning

if I have sum both day hours it should be 22 hrs.

Note - we have to exclude the weekend and holidays for the start to end date

 

[awoohaw]

Example:

Day 1 if the user logged at 8pm and they logged off at 7am next day then the work hour is 11
Day 2 if the user logged at 5pm and they logged off at 8am next day then the work hour is 11
Since the shift hours will start at 8pm and end in 7am in morning

if I have sum both day hours it should be 22 hrs.

Note - we have to exclude the weekend and holidays for the start to end date

this does not answer my questions.

 

[Keyan_rrr]

The shift starts at 8 PM and end at 7 AM next day.
so when the shift starts at 8PM Friday it ends at 7 AM on Saturday Morning.
shift starts at 8 PM on Monday and end at 7AM the next day. I hope this clear now

 

[Keyan_rrr]

deleted

What you mean by deleted?