Need excel help

rajesh1983

New Member
Joined
Jan 4, 2019
Messages
4
[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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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
Back
Top