Need excel help

R

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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.

Rijnsent said:
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
Click to expand...
 
Upvote 0
I'm able to get step 1 and step 2. Let me know how to achieve step 3

Rijnsent said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
458
etaf
etaf
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
707
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

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