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.

	A	B	C	D	E	F	G	H	I
1	DATE RANGE	WORK DURATION (HR)	DATES (EXCLUDING ALL HOLIDAYS)	WORK DURATION ON WORKDAYS (HR)	LIST OF HOLIDAYS	WORK DURATION HOLIDAY (HR)	ACTUAL WORK DAYS	PROGRESSIVE WK HR	Public holidays (other than weekly holidays)
2	5 NOV 19	8	5 NOV 19	8	8 NOV 19		5 NOV 19	8	10 NOV 19
3	6 NOV 19	6	6 NOV 19	6	9 NOV 19	10	6 NOV 19	14	12 NOV 19
4	7 NOV 19	7	7 NOV 19	7	10 NOV 19		7 NOV 19	21
5	8 NOV 19		11 NOV 19	9	12 NOV 19	2	9 NOV 19	31
6	9 NOV 19	10	13 NOV 19	10			11 NOV 19	40
7	10 NOV 19						12 NOV 19	42
8	11 NOV 19	9					13 NOV 19	52
9	12 NOV 19	2
10	13 NOV 19	10

<tbody>
</tbody>

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)),"")