working day details- Month wise diff kind of days details

[yaparala]

Hi

I have uploaded a sheet where you find date and holiday. i know how to get data month wise for all columns , but my requirement is i want select a month in drop down then all other column details should be updated automatically.

Month- drop down (Jan,Feb,Mar..)

Total Days
No of working days
No of non working days
Total WH
Total PH

PFA for more details

Sorry if i am asking irritating question in this forum.

Thanks
sreeni

 

[Marc L]

Hi !

Edit your post and suppress all unwanted attachments
in order to keep only one workbook, the right one … Thanks !
​

 

[bosco_yip]

1] Total Days, L3 :

=DAY(EOMONTH(1&L2,0))

2] No of working days, L4 :

=L3-L5

3] No of non working days, L5 :

=L6+L7

4] Total WH, L6 :

=SUMPRODUCT(--(OFFSET(D$1,MATCH(--(1&L$2),B$1:B$500,0)-1,,L$3)="WH"))

5] Total PH, L7 :

=SUMPRODUCT(--(OFFSET(D$1,MATCH(--(1&L$2),B$1:B$500,0)-1,,L$3)="PH"))

Regards