Listing holidays separately and finding values in aggregate

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
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
Code:
=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
Code:
=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.

ABCDEFGHI
1DATE RANGEWORK DURATION
(HR)
DATES
(EXCLUDING ALL HOLIDAYS)
WORK DURATION
ON WORKDAYS (HR)
LIST OF HOLIDAYSWORK DURATION HOLIDAY
(HR)
ACTUAL WORK DAYSPROGRESSIVE WK HRPublic holidays (other than weekly holidays)
25 NOV 1985 NOV 1988 NOV 195 NOV 19810 NOV 19
36 NOV 1966 NOV 1969 NOV 19106 NOV 191412 NOV 19
47 NOV 1977 NOV 19710 NOV 197 NOV 1921
58 NOV 1911 NOV 19912 NOV 1929 NOV 1931
69 NOV 191013 NOV 191011 NOV 1940
710 NOV 1912 NOV 1942
811 NOV 19913 NOV 1952
912 NOV 192
1013 NOV 1910

<tbody>
</tbody>

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

TIA
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
@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

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

That's not quite how the forum works.

Yeah....Sorry!

Please ignore #3 . Thanks for your help.
 

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
Thanks a lot!

Both worked.
 

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
@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

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,896
Messages
5,655,850
Members
418,247
Latest member
The_aze

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top