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

 

[jasonb75]

An alternative to enter into C2 and fill down to 10, this one will pick up a holiday or weekend in A2 rather than assuming it is always a working day. It is also error trapped to stop at the last date entered in A2:A10.

=IFERROR(VLOOKUP(WORKDAY.INTL($A$2-1,ROWS(C$2:C2),7,$I$2:$I$10),$A$2:$A$10,1,0),"")

Then this one in E2:E10 to do the same for the holidays and weekends.

=IFERROR(AGGREGATE(15,6,$A$2:$A$10/ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),ROWS(E$2:E2)),"")

Then you can finish off with =IF(C2="","",VLOOKUP(C2,$A$2:$B$10,2,0)) and =IF(E2="","",VLOOKUP(C2,$A$2:$B$10,2,0)) in columns D and F respectively.

 

[masud8956]

@jasonb75,

Thanks for the formulas you have given. All of them worked!

I am adding the link of my sample. Would you please do a little more favor.

https://www.dropbox.com/s/ovrzfe6fusq34we/Sample_1.xlsx?dl=0

The cells I need help with are filled in yellow.

What do I use for G2:H10?

In addition to that, I have added 3 more columns (K, L & M). I need to list only the wk days in Column L but need to get aggregate wk hr in Column M. Only thing is that, though the holidays are not shown in Column L, the wk hr in holidays also to be added to the next available working day at Column M. Actually I am stuck and need to use these Column L & M data in a chart as one of the series; that is why I am looking for this peculiar solution.

Thanks a lot!

 

[jasonb75]

That's not quite how the forum works.

An attachment should only be needed to clarify details on a complex question, your post should contain enough information to explain what you need without the file.

 

[masud8956]

That's not quite how the forum works.

Yeah....Sorry!

Please ignore #3 . Thanks for your help.

 

[masud8956]

An alternative to enter into C2 and fill down to 10, this one will pick up a holiday or weekend in A2 rather than assuming it is always a working day. It is also error trapped to stop at the last date entered in A2:A10.

=IFERROR(VLOOKUP(WORKDAY.INTL($A$2-1,ROWS(C$2:C2),7,$I$2:$I$10),$A$2:$A$10,1,0),"")

Then this one in E2:E10 to do the same for the holidays and weekends.

=IFERROR(AGGREGATE(15,6,$A$2:$A$10/ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),ROWS(E$2:E2)),"")

Then you can finish off with =IF(C2="","",VLOOKUP(C2,$A$2:$B$10,2,0)) and =IF(E2="","",VLOOKUP(C2,$A$2:$B$10,2,0)) in columns D and F respectively.

Sorry to bring this up again!

Is there a way around avoiding the "AGGREGATE" function for Column E? So that users of older versions will be able to use the formula?

 

[jasonb75]

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.

 

[masud8956]

Thanks a lot!

Both worked.

 

[masud8956]

@jasonb75,

Thanks for the solutions given at regarding NETWOKDAY.INTL alternatives today at:
https://www.mrexcel.com/forum/excel-questions/1114634-problem-networkday-intl-function.html

In this project also I have used NETWOKDAY.INTL twice. At my request you have modified the AGGREGATE function to be able to use it older Excel versions. But I did not notice that I also need a modification for C2 (Table at #1 ) which you have provided #2. I tried but no joy. My these two projects are very different.

Thanks

 

[jasonb75]

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