Time conversion with Vlookup with multiple conditions and criteria before computing the Rate

[ryanjuzz]

Hi Folks,

Appreciate your help here, I have few items needed below for my reports and refer to requirements and concerns.

Is there a way I can automatically compute the time at B (1st Worksheet) to the corresponding Engineer Grade and Rate per hour base from Engineer name (Section A)? My initial approach here; there is another worksheet (see below 2nd Worksheet) this is were Vlookup the Eng Name (1st Worksheet) and will compute the hours on B (1st Worksheet) base on the rate of the engineer on the scorecard (see worksheet 2 scorecard)l If this is the case I will no longer convert the time at B to C (1st Worksheet) to value below.

RESULTS NEEDED FOR REPORTING:
C - Time Effort - how can i compute from the B (HH:MM) that will convert this to data below (just for computation)
D - Engr Type (1st Worksheet) - will lookup the grade of engineer from worksheet 2 (i.e. SCOF) this will vlookup base from name
E - COST SUPPORT(1st worksheet) -will compute for the hours from B (or via C ) that will reference the Eng Name and will compute on engineer type (see sample below worksheet 1)



(WORKSHEET 2)
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Eng Name[/TD]
[TD]In HH:MM[/TD]
[TD]Time Effort[/TD]
[TD]Engr Type[/TD]
[TD]Cost Support[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]00:45[/TD]
[TD]0.75[/TD]
[TD]SCOF[/TD]
[TD]46.88
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tol[/TD]
[TD]01:55[/TD]
[TD]1.55[/TD]
[TD]COF[/TD]
[TD]87.58[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Joe[/TD]
[TD]03:15[/TD]
[TD]3.15[/TD]
[TD]COF[/TD]
[TD]177.98[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John[/TD]
[TD]02:11[/TD]
[TD]2.11[/TD]
[TD]SCOF[/TD]
[TD]131.88[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]















(WORKSHEET 2)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Consult Grade[/TD]
[TD]Descript[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]SCOF[/TD]
[TD]Sen Consult[/TD]
[TD]62.50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tol[/TD]
[TD]COF[/TD]
[TD]Consl Offshore[/TD]
[TD]56.50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Joe[/TD]
[TD]COF[/TD]
[TD]Consl Offshore[/TD]
[TD]56.50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Im not quite sure what column C is for and also why 01:55 should become 1.55? Id have thought it should be 1.9 or thereabouts?

 

[ryanjuzz]

yes sorry, you are correct @Steve It should be 1.9, my bad. anyway can you still help me on the D & E field? Thanks for noticing

 

[steve the fish]

In C2 its:

=B2*24 (format to number)

D2:

=VLOOKUP(A2,Sheet2!A:B,2,0)

E2:

=C2*VLOOKUP(D2,Sheet2!B:D,3,0)

 

[ryanjuzz]

thanks steve your the main man!!!