Listing holidays separately and finding values in aggregate

[masud8956]

Hello experts!

I don't know if it is too much to ask.

I have a situation like the following:

A2:A10 shows the timeline of a project. C2:C10 lists all working days between A2 and A10 excluding weekends (marked in red)and public holidays. C2=A2 but for C3 I have used

=WORKDAY.INTL(C2,1,7,$H$2:$H$10)

and copied it down to C10.

Public holidays (marked in blue) other than weekends are listed in I2:I10. Column B and D lists amount of day wise work hour by an employee.

In D2:D10 I have used the formula

=VLOOKUP(C2,$A$2:$B$10,2)

What I am looking for is:

1. A formula at E2:E10 to list all holidays between A2:A10 automatically and a formula at F2:F10 to list the work hours basing on E2:E10 date.

2. I also need a formula at G2:H10 to list all the dates on which the employee actually worked (including holidays) along with the work hours in progressive format.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]DATE RANGE[/TD]
[TD="align: center"]WORK DURATION
(HR)[/TD]
[TD="align: center"]DATES
(EXCLUDING ALL HOLIDAYS)[/TD]
[TD="align: center"]WORK DURATION
ON WORKDAYS (HR)[/TD]
[TD="align: center"]LIST OF HOLIDAYS[/TD]
[TD="align: center"]WORK DURATION HOLIDAY
(HR)[/TD]
[TD="align: center"]ACTUAL WORK DAYS[/TD]
[TD="align: center"]PROGRESSIVE WK HR[/TD]
[TD="align: center"]Public holidays (other than weekly holidays)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5 NOV 19[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]5 NOV 19[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5 NOV 19[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10 NOV 19[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]6 NOV 19[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6 NOV 19[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9 NOV 19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6 NOV 19[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]12 NOV 19[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]7 NOV 19[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7 NOV 19[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]10 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7 NOV 19[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11 NOV 19[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12 NOV 19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9 NOV 19[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9 NOV 19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13 NOV 19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11 NOV 19[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]10 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12 NOV 19[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]11 NOV 19[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]13 NOV 19[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]12 NOV 19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]13 NOV 19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is it possible? I do not prefer VBA unless there is no other way around.

TIA

 

[masud8956]

Thanks!

Works perfectly.

 

[masud8956]

For excel 2007, array confirmed with Ctrl Shift Enter.

=IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),$A$2:$A$10),ROWS(E$2:E2)),"")

For excel 2003, array confirmed with Shift Ctrl Enter.

=IF(ISNUMBER(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),$A$2:$A$10),ROWS(E$2:E2))),SMALL(IF(ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),$A$2:$A$10),ROWS(E$2:E2)),"")

I believe that the 2003 formula should work with even older versions, but I am not able to confirm that.

I did not notice earlier. You have provided the formulas above for E2 at table #1 , these formula only returns the weekends. In Column E I need to list all holidays. To account for public holidays listed at Column E what modification do I need?

 

[masud8956]

I think I found the problem. Nothing wrong with the code for E2. For C2 (on which E2 is dependent), the Public Holidays were listed in merged cells. Once I unmerged, the formula for C2 worked, thereby E2 worked too.

Your solution for C2 is quoted below.

As before, these will need array confirming.

Excel 2007

=IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$I$2:$I$10,0)),IF(WEEKDAY($A$2:$A$10)<6,$A$2:$A$10)),ROWS(C$2:C2)),"")

Excel 2003 or older

=IF(ISNUMBER(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$I$2:$I$10,0)),IF(WEEKDAY($A$2:$A$10)<6,$A$2:$A$10)),ROWS(C$2:C2))),SMALL(IF(ISERROR(MATCH($A$2:$A$10,$I$2:$I$10,0)),IF(WEEKDAY($A$2:$A$10)<6,$A$2:$A$10)),ROWS(C$2:C2)),"")