masud8956
Board Regular
- Joined
- Oct 22, 2016
- Messages
- 163
- Office Version
- 2016
- 2011
- 2007
- Platform
- 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
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
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.
<tbody>
</tbody>
Is it possible? I do not prefer VBA unless there is no other way around.
TIA
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)
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.
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
Last edited: