Excel Help

[rajesh1983]

Table 1: order ID Name Live Date Hours Hours occupied as of today 121 Rajesh 01/19/2019 40 ? 122 Rajesh 01/19/2019 60 ? 123 Rajesh 01/19/2019 20 ? 124 Rajesh 02/16/2019 100 ? 125 Ramesh 01/19/2019 120 ? 126 Ramesh 01/19/2019 160 ? 127 Ramesh 01/19/2019 25 ? 128 Ramesh 03/30/2019 30 ? 129 Suresh 01/19/2019 10 ? 130 Suresh 02/16/2019 80 ? 131 Suresh 02/16/2019 90 ? 132 Suresh 03/30/2019 30 ? 133 Naresh 01/19/2019 20 ? 134 Naresh 02/16/2019 100 ? 135 Naresh 02/16/2019 120 ? 136 Naresh 03/30/2019 60 ? <colgroup><col span="2"><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Table 2: (Data in table 2 will be constant, it is fixed)

Row ID	Reg_start	Reg_End	Work_Start	Work_End	Test_Start	Test_End	Live Date
1	11/28/2018	12/08/2018	12/09/2018	12/27/2018	12/29/2018	01/17/2019	01/19/2019
2	12/12/2018	12/22/2018	12/23/2018	01/10/2019	01/12/2019	01/31/2019	02/02/2019
3	12/26/2018	01/05/2019	01/06/2019	01/25/2019	01/26/2019	02/14/2019	02/16/2019
4	01/09/2019	01/20/2019	01/20/2019	02/07/2019	02/09/2019	02/28/2019	03/02/2019
5	01/22/2019	02/01/2019	02/02/2019	02/22/2019	02/23/2019	03/14/2019	03/16/2019
6	02/06/2019	02/16/2019	02/17/2019	03/07/2019	03/09/2019	03/28/2019	03/30/2019
7	02/27/2019	03/09/2019	03/10/2019	03/28/2019	03/30/2019	04/18/2019	04/20/2019
8	03/13/2019	03/23/2019	03/24/2019	04/11/2019	04/13/2019	05/02/2019	05/04/2019
9	03/27/2019	04/06/2019	04/07/2019	04/25/2019	04/27/2019	05/16/2019	05/18/2019
10	04/09/2019	04/19/2019	04/20/2019	05/08/2019	05/10/2019	05/30/2019	06/01/2019
11	04/30/2019	05/10/2019	05/11/2019	05/31/2019	06/01/2019	06/20/2019	06/22/2019
12	05/14/2019	05/24/2019	05/25/2019	06/12/2019	06/14/2019	07/03/2019	07/06/2019
13	05/29/2019	06/08/2019	06/09/2019	06/27/2019	06/29/2019	07/18/2019	07/20/2019
14	06/12/2019	06/22/2019	06/23/2019	07/11/2019	07/13/2019	08/01/2019	08/03/2019
15	07/03/2019	07/13/2019	07/14/2019	08/01/2019	08/03/2019	08/22/2019	08/24/2019
16	07/16/2019	07/26/2019	07/27/2019	08/14/2019	08/16/2019	09/05/2019	09/07/2019
17	07/31/2019	08/10/2019	08/11/2019	08/29/2019	08/31/2019	09/19/2019	09/21/2019
18	08/14/2019	08/24/2019	08/25/2019	09/12/2019	09/14/2019	10/03/2019	10/05/2019
19	08/27/2019	09/06/2019	09/07/2019	09/25/2019	09/27/2019	10/17/2019	10/19/2019
20	09/11/2019	09/21/2019	09/22/2019	10/10/2019	10/12/2019	10/31/2019	11/02/2019
21	09/24/2019	10/04/2019	10/05/2019	10/23/2019	10/25/2019	11/14/2019	11/16/2019
22	10/08/2019	10/18/2019	10/19/2019	11/06/2019	11/08/2019	11/27/2019	11/30/2019
23	10/23/2019	11/02/2019	11/03/2019	11/21/2019	11/23/2019	12/12/2019	12/14/2019

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

1. Hours required between Reg_start and Reg_End is 5% of hours
2. Hours required between Work_Start and Work_End is 95% of hours
3. Hours required between Test_Start and Test_End is 5% of hours

My requirement is if any day i run the report in table1.Hours occupied as of today i should get the number of hours occupied.

let see couple of examples

Example 1 for order id 121, Hours is 40 and Live date is 01/19/2019.

Table 1:

order ID	Name	Live Date	Hours	Hours occupied as of today
121	Rajesh	01/19/2019	40	?

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

In table 2 this row matches with row ID 1

Row ID	Reg_start	Reg_End	Work_Start	Work_End	Test_Start	Test_End	Live Date
1	11/28/2018	12/08/2018	12/09/2018	12/27/2018	12/29/2018	01/17/2019	01/19/2019

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

when i generate report as of today (Today date is 01/04/2019)

• Reg_start and Reg_End dates are already passed so it should return 0 hours. (This has 5% value. 5% of 40 hrs)
• Work_Start and Work_End dates are also already passed it should return 0 hours. (This has 90% value. 90% of 40 hrs)
• Today's date falls between Test_Start and Test_End so it should return 2 hrs This has 5% value. 5% of 40 hrs =2hrs)

Example 2 for order id 124, Hours is 100 and Live date is 02/16/2019.

Table 1:

order ID	Name	Live Date	Hours	Hours occupied as of today
124	Rajesh	02/16/2019	100	?

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

In table 2 this row matches with row ID 3.

Row ID	Reg_start	Reg_End	Work_Start	Work_End	Test_Start	Test_End	Live Date
3	12/26/2018	01/05/2019	01/06/2019	01/25/2019	01/26/2019	02/14/2019	02/16/2019

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

when i generate report as of today (Today date is 01/04/2019)

• Today's date falls between Reg_start and Reg_End dates, hence it should return 5 hours. (This has 5% value. 5% of 100 hrs= 5hrs)
• Work_Start and Work_End dates in future, hence it should return 0 hours. (This has 90% value. 90% of 100 hrs)
• Test_Start and Test_End dates is also in future, hence it should return 0 hrs (This has 5% value. 5% of 100 hrs ).

please help me to achieve this.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1082602-need-excel-help.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.