Working hrs. computation

[PrinceExcel]

I am trying to create a report where working hrs. is computed.
Ex.

8/30/2019 4:00 PM - 09/02/2019 9:00 AM

Working hrs. 8 am - 5 pm

I want to have a result of 2 hrs. Since worked it done in 8/30/19 4-5pm and 9/2/19 8-9am only. 8/31/19 & 9/1/19 are weekends. Also I want to exclude hrs in night time (downtime) and holidays/weekends.

Please help.

 

[tyija1995]

Hey,

Try this:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]StartDate[/TD]
[TD]EndDate[/TD]
[TD]StartDateTime[/TD]
[TD]EndDateTime[/TD]
[TD]Working Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30/08/2019[/TD]
[TD]02/09/2019[/TD]
[TD]16:00[/TD]
[TD]09:00[/TD]
[TD]2.00[/TD]
[/TR]
</tbody>[/TABLE]


Where A2:D2 are manually entered, if A2 and B2 contain the time in them as well (e.g. A2 = 30/08/2019 16:00) then use formulas to generate just the time in cells C2 & D2 as such:

C2 (drag across to D2)
=A2-ROUNDDOWN(A2,0)

Once set up, then in E2:

SUM((NETWORKDAYS(A2,B2)-SUMPRODUCT((WEEKDAY(A2)>1)*(WEEKDAY(A2)<7)+(WEEKDAY(B2)>1)*(WEEKDAY(B2)<7)))*9,IF(AND(WEEKDAY(A2)>1,WEEKDAY(A2)<7),IF(C2<8/24,9,IF(C2>17/24,0,17/24-C2)*24)),IF(AND(WEEKDAY(B2)>1,WEEKDAY(B2)<7),IF(D2<8/24,0,IF(D2>17/24,9,(D2-8/24)*24))))

 

[tyija1995]

If you want to account for the holidays then make a list of dates that represent the holidays, give it a named range "holidays" and then try this formula again in E2:

SUM((NETWORKDAYS(A2,B2,holidays)-SUMPRODUCT((SUMPRODUCT(--(A2=holidays))=0)*(WEEKDAY(A2)>1)*(WEEKDAY(A2)<7)+((SUMPRODUCT(--(B2=holidays))=0)*WEEKDAY(B2)>1)*(WEEKDAY(B2)<7)))*9,IF(AND(WEEKDAY(A2)>1,WEEKDAY(A2)<7,SUMPRODUCT(--(A2=holidays))=0),IF(C2<8/24,9,IF(C2>17/24,0,17/24-C2)*24)),IF(AND(WEEKDAY(B2)>1,WEEKDAY(B2)<7,SUMPRODUCT(--(B2=holidays))=0),IF(D2<8/24,0,IF(D2>17/24,9,(D2-8/24)*24))))

E.g. If you try StartDate as 25/12/2019 and EndDate as 26/12/2019, if these 2 dates are in the holidays named range then the result will be 0, as expected.

 

[PrinceExcel]

Thanks. I will try this once tommorrow. Is there any short formula for this one?

 

[PrinceExcel]

Tried but it doesnt work &#55357;&#56867;

 

[tyija1995]

What does the formula return for you? What exactly did you try?

 

[PrinceExcel]

I think i made something wrong with the formula..do you have the excel file on this formula? Please send it to me andrewdiokno91@gmail.com.

Thank you very much.