Need Formula

[PK1988]

Hi, I have a data downloaded from the erp, need to calculate the Average working hrs, no of days (excluding weekends) and no.of hrs and no.of days in weekend.
Need formula to get the above information. result table will look like below
Also we should not consider less than 3 hrs data.

erp data

 

[PK1988]

Hi Can any one support on my above query.

 

[Michael M]

Youd probably get a response of you used the XL2BB addin to post data that could be worked with.
See the download in my sig

 

[Michael M]

Maybe something like

=Averageif(Name,"apple",date)

 

[Tetra201]

Assuming your downloaded data are on the 'ERP' worksheet, see if the following formulas work for you:
No of Hrs (Working Days)

=SUMPRODUCT(--(ERP!$A$2:$A$999=C2),--(TEXT(ERP!$C$2:$C$999,"mmm'yy")=TEXT(B2,"mmm'yy")),NETWORKDAYS(+ERP!$C$2:$C$999,+ERP!$C$2:$C$999),--(ERP!$D$2:$D$999>=3),ERP!$D$2:$D$999)

No of days (Working Days)

=SUMPRODUCT(--(ERP!$A$2:$A$999=C2),--(TEXT(ERP!$C$2:$C$999,"mmm'yy")=TEXT(B2,"mmm'yy")),NETWORKDAYS(+ERP!$C$2:$C$999,+ERP!$C$2:$C$999),--(ERP!$D$2:$D$999>=3))

No of Hrs (Weekend)

=SUMPRODUCT(--(ERP!$A$2:$A$999=C2),--(TEXT(ERP!$C$2:$C$999,"mmm'yy")=TEXT(B2,"mmm'yy")),NETWORKDAYS.INTL(+ERP!$C$2:$C$999,+ERP!$C$2:$C$999,"1111100"),--(ERP!$D$2:$D$999>=3),ERP!$D$2:$D$999)

No of Days (Weekend)

=SUMPRODUCT(--(ERP!$A$2:$A$999=C2),--(TEXT(ERP!$C$2:$C$999,"mmm'yy")=TEXT(B2,"mmm'yy")),NETWORKDAYS.INTL(+ERP!$C$2:$C$999,+ERP!$C$2:$C$999,"1111100"),--(ERP!$D$2:$D$999>=3))