Need excel help

[rajesh1983]

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

Table 2: (Data in table 2 will be constant, it is fixed)
[TABLE="width: 727"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row ID[/TD]
[TD]Reg_start[/TD]
[TD]Reg_End[/TD]
[TD]Work_Start[/TD]
[TD]Work_End[/TD]
[TD]Test_Start[/TD]
[TD]Test_End[/TD]
[TD]Live Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/28/2018[/TD]
[TD]12/08/2018[/TD]
[TD]12/09/2018[/TD]
[TD]12/27/2018[/TD]
[TD]12/29/2018[/TD]
[TD]01/17/2019[/TD]
[TD]01/19/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/12/2018[/TD]
[TD]12/22/2018[/TD]
[TD]12/23/2018[/TD]
[TD]01/10/2019[/TD]
[TD]01/12/2019[/TD]
[TD]01/31/2019[/TD]
[TD]02/02/2019[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/26/2018[/TD]
[TD]01/05/2019[/TD]
[TD]01/06/2019[/TD]
[TD]01/25/2019[/TD]
[TD]01/26/2019[/TD]
[TD]02/14/2019[/TD]
[TD]02/16/2019[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/09/2019[/TD]
[TD]01/20/2019[/TD]
[TD]01/20/2019[/TD]
[TD]02/07/2019[/TD]
[TD]02/09/2019[/TD]
[TD]02/28/2019[/TD]
[TD]03/02/2019[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]01/22/2019[/TD]
[TD]02/01/2019[/TD]
[TD]02/02/2019[/TD]
[TD]02/22/2019[/TD]
[TD]02/23/2019[/TD]
[TD]03/14/2019[/TD]
[TD]03/16/2019[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/06/2019[/TD]
[TD]02/16/2019[/TD]
[TD]02/17/2019[/TD]
[TD]03/07/2019[/TD]
[TD]03/09/2019[/TD]
[TD]03/28/2019[/TD]
[TD]03/30/2019[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/27/2019[/TD]
[TD]03/09/2019[/TD]
[TD]03/10/2019[/TD]
[TD]03/28/2019[/TD]
[TD]03/30/2019[/TD]
[TD]04/18/2019[/TD]
[TD]04/20/2019[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]03/13/2019[/TD]
[TD]03/23/2019[/TD]
[TD]03/24/2019[/TD]
[TD]04/11/2019[/TD]
[TD]04/13/2019[/TD]
[TD]05/02/2019[/TD]
[TD]05/04/2019[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]03/27/2019[/TD]
[TD]04/06/2019[/TD]
[TD]04/07/2019[/TD]
[TD]04/25/2019[/TD]
[TD]04/27/2019[/TD]
[TD]05/16/2019[/TD]
[TD]05/18/2019[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]04/09/2019[/TD]
[TD]04/19/2019[/TD]
[TD]04/20/2019[/TD]
[TD]05/08/2019[/TD]
[TD]05/10/2019[/TD]
[TD]05/30/2019[/TD]
[TD]06/01/2019[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]04/30/2019[/TD]
[TD]05/10/2019[/TD]
[TD]05/11/2019[/TD]
[TD]05/31/2019[/TD]
[TD]06/01/2019[/TD]
[TD]06/20/2019[/TD]
[TD]06/22/2019[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]05/14/2019[/TD]
[TD]05/24/2019[/TD]
[TD]05/25/2019[/TD]
[TD]06/12/2019[/TD]
[TD]06/14/2019[/TD]
[TD]07/03/2019[/TD]
[TD]07/06/2019[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]05/29/2019[/TD]
[TD]06/08/2019[/TD]
[TD]06/09/2019[/TD]
[TD]06/27/2019[/TD]
[TD]06/29/2019[/TD]
[TD]07/18/2019[/TD]
[TD]07/20/2019[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]06/12/2019[/TD]
[TD]06/22/2019[/TD]
[TD]06/23/2019[/TD]
[TD]07/11/2019[/TD]
[TD]07/13/2019[/TD]
[TD]08/01/2019[/TD]
[TD]08/03/2019[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]07/03/2019[/TD]
[TD]07/13/2019[/TD]
[TD]07/14/2019[/TD]
[TD]08/01/2019[/TD]
[TD]08/03/2019[/TD]
[TD]08/22/2019[/TD]
[TD]08/24/2019[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]07/16/2019[/TD]
[TD]07/26/2019[/TD]
[TD]07/27/2019[/TD]
[TD]08/14/2019[/TD]
[TD]08/16/2019[/TD]
[TD]09/05/2019[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]07/31/2019[/TD]
[TD]08/10/2019[/TD]
[TD]08/11/2019[/TD]
[TD]08/29/2019[/TD]
[TD]08/31/2019[/TD]
[TD]09/19/2019[/TD]
[TD]09/21/2019[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]08/14/2019[/TD]
[TD]08/24/2019[/TD]
[TD]08/25/2019[/TD]
[TD]09/12/2019[/TD]
[TD]09/14/2019[/TD]
[TD]10/03/2019[/TD]
[TD]10/05/2019[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]08/27/2019[/TD]
[TD]09/06/2019[/TD]
[TD]09/07/2019[/TD]
[TD]09/25/2019[/TD]
[TD]09/27/2019[/TD]
[TD]10/17/2019[/TD]
[TD]10/19/2019[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]09/11/2019[/TD]
[TD]09/21/2019[/TD]
[TD]09/22/2019[/TD]
[TD]10/10/2019[/TD]
[TD]10/12/2019[/TD]
[TD]10/31/2019[/TD]
[TD]11/02/2019[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]09/24/2019[/TD]
[TD]10/04/2019[/TD]
[TD]10/05/2019[/TD]
[TD]10/23/2019[/TD]
[TD]10/25/2019[/TD]
[TD]11/14/2019[/TD]
[TD]11/16/2019[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]10/08/2019[/TD]
[TD]10/18/2019[/TD]
[TD]10/19/2019[/TD]
[TD]11/06/2019[/TD]
[TD]11/08/2019[/TD]
[TD]11/27/2019[/TD]
[TD]11/30/2019[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]10/23/2019[/TD]
[TD]11/02/2019[/TD]
[TD]11/03/2019[/TD]
[TD]11/21/2019[/TD]
[TD]11/23/2019[/TD]
[TD]12/12/2019[/TD]
[TD]12/14/2019[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 538"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]order ID[/TD]
[TD]Name[/TD]
[TD]Live Date[/TD]
[TD]Hours[/TD]
[TD]Hours occupied as of today[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]Rajesh[/TD]
[TD]01/19/2019[/TD]
[TD]40[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

In table 2 this row matches with row ID 1
[TABLE="width: 758"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row ID[/TD]
[TD]Reg_start[/TD]
[TD]Reg_End[/TD]
[TD]Work_Start[/TD]
[TD]Work_End[/TD]
[TD]Test_Start[/TD]
[TD]Test_End[/TD]
[TD]Live Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/28/2018[/TD]
[TD]12/08/2018[/TD]
[TD]12/09/2018[/TD]
[TD]12/27/2018[/TD]
[TD]12/29/2018[/TD]
[TD]01/17/2019[/TD]
[TD]01/19/2019

[/TD]
[/TR]
</tbody>[/TABLE]
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:

[TABLE="width: 538"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]order ID[/TD]
[TD]Name[/TD]
[TD]Live Date[/TD]
[TD]Hours[/TD]
[TD]Hours occupied as of today[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]Rajesh[/TD]
[TD]02/16/2019[/TD]
[TD]100[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

In table 2 this row matches with row ID 3.

[TABLE="width: 758"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row ID[/TD]
[TD]Reg_start[/TD]
[TD]Reg_End[/TD]
[TD]Work_Start[/TD]
[TD]Work_End[/TD]
[TD]Test_Start[/TD]
[TD]Test_End[/TD]
[TD]Live Date[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/26/2018[/TD]
[TD]01/05/2019[/TD]
[TD]01/06/2019[/TD]
[TD]01/25/2019[/TD]
[TD]01/26/2019[/TD]
[TD]02/14/2019[/TD]
[TD]02/16/2019

[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Rijnsent]

Hi Rajesh,
do you already have some formulas? If I cut your question into bits:
step 1: find the Live Date in tabel 2 -> =MATCH(C2,I2:I100,0) -> this should give back the row where the data is, let's call it RES_01
step 2: with that row, try to determine in which stage the project is, a key formula for that could be OFFSET, using the result of the previous formula. -> IF(TODAY()>OFFSET($G$1,RES_01,0),"LIVE",IF(TODAY()>=OFFSET($F$1,RES_01,0),"TEST",IF(TODAY()>=OFFSET($D$1,RES_01,0),"WORK",IF(TODAY()>=OFFSET($B$1,RES_01,0),"REG","UNKNOWN"))))
step 3: use that result to calculate the right amount of hours.

Hope that helps,

Koen

 

[rajesh1983]

Step 2 formula returns me False for my first row of table 1.

Hi Rajesh,
do you already have some formulas? If I cut your question into bits:
step 1: find the Live Date in tabel 2 -> =MATCH(C2,I2:I100,0) -> this should give back the row where the data is, let's call it RES_01
step 2: with that row, try to determine in which stage the project is, a key formula for that could be OFFSET, using the result of the previous formula. -> IF(TODAY()>OFFSET($G$1,RES_01,0),"LIVE",IF(TODAY()>=OFFSET($F$1,RES_01,0),"TEST",IF(TODAY()>=OFFSET($D$1,RES_01,0),"WORK",IF(TODAY()>=OFFSET($B$1,RES_01,0),"REG","UNKNOWN"))))
step 3: use that result to calculate the right amount of hours.

Hope that helps,

Koen

 

[rajesh1983]

I'm able to get step 1 and step 2. Let me know how to achieve step 3

Hi Rajesh,
do you already have some formulas? If I cut your question into bits:
step 1: find the Live Date in tabel 2 -> =MATCH(C2,I2:I100,0) -> this should give back the row where the data is, let's call it RES_01
step 2: with that row, try to determine in which stage the project is, a key formula for that could be OFFSET, using the result of the previous formula. -> IF(TODAY()>OFFSET($G$1,RES_01,0),"LIVE",IF(TODAY()>=OFFSET($F$1,RES_01,0),"TEST",IF(TODAY()>=OFFSET($D$1,RES_01,0),"WORK",IF(TODAY()>=OFFSET($B$1,RES_01,0),"REG","UNKNOWN"))))
step 3: use that result to calculate the right amount of hours.

Hope that helps,

Koen