Listing holidays separately and finding values in aggregate

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
147
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,930
Office Version
2019
Platform
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
147
@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
6,930
Office Version
2019
Platform
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
147
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
6,930
Office Version
2019
Platform
Windows
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
147
@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
6,930
Office Version
2019
Platform
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)),"")
 

Forum statistics

Threads
1,078,486
Messages
5,340,618
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top