Look up employee rate based on date range

[egrospeRP]

Hello Excel Experts,
I have two tables in my excel sheet. First table is a labor detail containing date, employee ID, and employee name. This table contains transactional items when an employee enters their time.

The other table contains employee rate history. This table outlines employee ID, Employee name, hourly rate, effective date, and ineffective date.

What i'm trying to do is pull the employee hourly rate at the time the entry was made on the labor detail table. I'm using the following formula but it keeps returning result to 0.

I'm stuck and need help!

Labor Detail.xlsx
	A	B	C	D
1	Date	Employee ID	Employee	Labor Rate (lookup)
2	4/1/2021	100	Employee 1	0
3	4/2/2021	200	Employee 2	0
4	4/3/2021	200	Employee 2	0
5	4/4/2021	200	Employee 2	0
6	4/5/2021	200	Employee 2	0
7	4/6/2021	300	Employee 3	0
8	4/7/2021	300	Employee 3	0
9	4/8/2021	300	Employee 3	0
10	4/9/2021	300	Employee 3	0
11	4/10/2021	100	Employee 1	0
12	4/11/2021	300	Employee 3	0
13	4/12/2021	300	Employee 3	0
14	4/13/2021	200	Employee 2	0
15	4/14/2021	300	Employee 3	0
16	4/15/2021	100	Employee 1	0
17	4/16/2021	100	Employee 1	0
18	4/17/2021	100	Employee 1	0
19	4/18/2021	100	Employee 1	0
20	4/19/2021	100	Employee 1	0
21	4/20/2021	100	Employee 1	0
22	4/21/2021	100	Employee 1	0
23	4/22/2021	300	Employee 3	0
24	4/23/2021	300	Employee 3	0
25	4/24/2021	300	Employee 3	0
26	4/25/2021	300	Employee 3	0
27	4/26/2021	300	Employee 3	0
28	4/27/2021	300	Employee 3	0
29	4/28/2021	300	Employee 3	0
30	4/29/2021	300	Employee 3	0
31	4/30/2021	300	Employee 3	0
32	5/1/2021	100	Employee 1	0
33	5/2/2021	300	Employee 3	0
34	5/3/2021	300	Employee 3	0
35	5/4/2021	300	Employee 3	0
36	5/5/2021	300	Employee 3	0
37	5/6/2021	300	Employee 3	0
38	5/7/2021	300	Employee 3	0
39	5/8/2021	300	Employee 3	0
40	5/9/2021	100	Employee 1	0
Labor Detail

Cell Formulas
Range	Formula
D2:D40	D2	=SUMIFS(Table4[Rate],Table4[Employee ID],[@[Employee ID]],Table4[Effective Date],">="&[@Date],Table4[Ineffective Date],"<="&[@Date])

Labor Detail.xlsx
	A	B	C	D	E
1	Employee ID	Employee	Rate	Effective Date	Ineffective Date
2	100	Employee 1	$ 25.00	4/1/2021	9/30/2021
3	200	Employee 2	$ 30.00	4/1/2021	6/30/2021
4	200	Employee 2	$ 40.00	7/1/2021	9/30/2021
5	300	Employee 3	$ 20.00	4/1/2021	5/30/2021
6	300	Employee 3	$ 45.00	6/1/2021	6/25/2021
7	300	Employee 3	$ 60.00	6/26/2021	9/30/2021
Rate History

 

[alansidman]

using power query, bring each table into the PQE and then join them on the EE ID. Here are the Mcodes for each and then the join

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee ID", type text}, {"Employee", type text}})
in
    #"Changed Type"

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective Date", type date}, {"Ineffective Date", type date}, {" Rate ", type number}, {"Employee ID", type text}, {"Employee", type text}})
in
    #"Changed Type"

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, Table2, {"Employee ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {" Rate ", "Effective Date", "Ineffective Date"}, {"Table2. Rate ", "Table2.Effective Date", "Table2.Ineffective Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if[Date] >=[Table2.Effective Date] and [Date] <[Table2.Ineffective Date] then[#"Table2. Rate "] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table2. Rate ", "Table2.Effective Date", "Table2.Ineffective Date"})
in
    #"Removed Columns"

Book3
	F	G	H	I
10	Date	Employee ID	Employee	Custom
11	4/1/2021	100	Employee 1	25
12	4/2/2021	200	Employee 2	30
13	4/3/2021	200	Employee 2	30
14	4/4/2021	200	Employee 2	30
15	4/5/2021	200	Employee 2	30
16	4/6/2021	300	Employee 3	20
17	4/7/2021	300	Employee 3	20
18	4/8/2021	300	Employee 3	20
19	4/9/2021	300	Employee 3	20
20	4/10/2021	100	Employee 1	25
21	4/11/2021	300	Employee 3	20
22	4/12/2021	300	Employee 3	20
23	4/13/2021	200	Employee 2	30
24	4/14/2021	300	Employee 3	20
25	4/15/2021	100	Employee 1	25
26	4/16/2021	100	Employee 1	25
27	4/17/2021	100	Employee 1	25
28	4/18/2021	100	Employee 1	25
29	4/19/2021	100	Employee 1	25
30	4/20/2021	100	Employee 1	25
31	4/21/2021	100	Employee 1	25
32	4/22/2021	300	Employee 3	20
33	4/23/2021	300	Employee 3	20
34	4/24/2021	300	Employee 3	20
35	4/25/2021	300	Employee 3	20
36	4/26/2021	300	Employee 3	20
37	4/27/2021	300	Employee 3	20
38	4/28/2021	300	Employee 3	20
39	4/29/2021	300	Employee 3	20
40	4/30/2021	300	Employee 3	20
41	5/1/2021	100	Employee 1	25
42	5/2/2021	300	Employee 3	20
43	5/3/2021	300	Employee 3	20
44	5/4/2021	300	Employee 3	20
45	5/5/2021	300	Employee 3	20
46	5/6/2021	300	Employee 3	20
47	5/7/2021	300	Employee 3	20
48	5/8/2021	300	Employee 3	20
49	5/9/2021	100	Employee 1	25
Sheet1

 

[egrospeRP]

using power query, bring each table into the PQE and then join them on the EE ID. Here are the Mcodes for each and then the join

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee ID", type text}, {"Employee", type text}})
in
    #"Changed Type"

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Effective Date", type date}, {"Ineffective Date", type date}, {" Rate ", type number}, {"Employee ID", type text}, {"Employee", type text}})
in
    #"Changed Type"

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, Table2, {"Employee ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {" Rate ", "Effective Date", "Ineffective Date"}, {"Table2. Rate ", "Table2.Effective Date", "Table2.Ineffective Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if[Date] >=[Table2.Effective Date] and [Date] <[Table2.Ineffective Date] then[#"Table2. Rate "] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table2. Rate ", "Table2.Effective Date", "Table2.Ineffective Date"})
in
    #"Removed Columns"

Book3
	F	G	H	I
10	Date	Employee ID	Employee	Custom
11	4/1/2021	100	Employee 1	25
12	4/2/2021	200	Employee 2	30
13	4/3/2021	200	Employee 2	30
14	4/4/2021	200	Employee 2	30
15	4/5/2021	200	Employee 2	30
16	4/6/2021	300	Employee 3	20
17	4/7/2021	300	Employee 3	20
18	4/8/2021	300	Employee 3	20
19	4/9/2021	300	Employee 3	20
20	4/10/2021	100	Employee 1	25
21	4/11/2021	300	Employee 3	20
22	4/12/2021	300	Employee 3	20
23	4/13/2021	200	Employee 2	30
24	4/14/2021	300	Employee 3	20
25	4/15/2021	100	Employee 1	25
26	4/16/2021	100	Employee 1	25
27	4/17/2021	100	Employee 1	25
28	4/18/2021	100	Employee 1	25
29	4/19/2021	100	Employee 1	25
30	4/20/2021	100	Employee 1	25
31	4/21/2021	100	Employee 1	25
32	4/22/2021	300	Employee 3	20
33	4/23/2021	300	Employee 3	20
34	4/24/2021	300	Employee 3	20
35	4/25/2021	300	Employee 3	20
36	4/26/2021	300	Employee 3	20
37	4/27/2021	300	Employee 3	20
38	4/28/2021	300	Employee 3	20
39	4/29/2021	300	Employee 3	20
40	4/30/2021	300	Employee 3	20
41	5/1/2021	100	Employee 1	25
42	5/2/2021	300	Employee 3	20
43	5/3/2021	300	Employee 3	20
44	5/4/2021	300	Employee 3	20
45	5/5/2021	300	Employee 3	20
46	5/6/2021	300	Employee 3	20
47	5/7/2021	300	Employee 3	20
48	5/8/2021	300	Employee 3	20
49	5/9/2021	100	Employee 1	25
Sheet1

H

Hello Excel Experts,
I have two tables in my excel sheet. First table is a labor detail containing date, employee ID, and employee name. This table contains transactional items when an employee enters their time.

The other table contains employee rate history. This table outlines employee ID, Employee name, hourly rate, effective date, and ineffective date.

What i'm trying to do is pull the employee hourly rate at the time the entry was made on the labor detail table. I'm using the following formula but it keeps returning result to 0.

I'm stuck and need help!

Labor Detail.xlsx
	A	B	C	D
1	Date	Employee ID	Employee	Labor Rate (lookup)
2	4/1/2021	100	Employee 1	0
3	4/2/2021	200	Employee 2	0
4	4/3/2021	200	Employee 2	0
5	4/4/2021	200	Employee 2	0
6	4/5/2021	200	Employee 2	0
7	4/6/2021	300	Employee 3	0
8	4/7/2021	300	Employee 3	0
9	4/8/2021	300	Employee 3	0
10	4/9/2021	300	Employee 3	0
11	4/10/2021	100	Employee 1	0
12	4/11/2021	300	Employee 3	0
13	4/12/2021	300	Employee 3	0
14	4/13/2021	200	Employee 2	0
15	4/14/2021	300	Employee 3	0
16	4/15/2021	100	Employee 1	0
17	4/16/2021	100	Employee 1	0
18	4/17/2021	100	Employee 1	0
19	4/18/2021	100	Employee 1	0
20	4/19/2021	100	Employee 1	0
21	4/20/2021	100	Employee 1	0
22	4/21/2021	100	Employee 1	0
23	4/22/2021	300	Employee 3	0
24	4/23/2021	300	Employee 3	0
25	4/24/2021	300	Employee 3	0
26	4/25/2021	300	Employee 3	0
27	4/26/2021	300	Employee 3	0
28	4/27/2021	300	Employee 3	0
29	4/28/2021	300	Employee 3	0
30	4/29/2021	300	Employee 3	0
31	4/30/2021	300	Employee 3	0
32	5/1/2021	100	Employee 1	0
33	5/2/2021	300	Employee 3	0
34	5/3/2021	300	Employee 3	0
35	5/4/2021	300	Employee 3	0
36	5/5/2021	300	Employee 3	0
37	5/6/2021	300	Employee 3	0
38	5/7/2021	300	Employee 3	0
39	5/8/2021	300	Employee 3	0
40	5/9/2021	100	Employee 1	0
Labor Detail

Cell Formulas
Range	Formula
D2:D40	D2	=SUMIFS(Table4[Rate],Table4[Employee ID],[@[Employee ID]],Table4[Effective Date],">="&[@Date],Table4[Ineffective Date],"<="&[@Date])

Labor Detail.xlsx
	A	B	C	D	E
1	Employee ID	Employee	Rate	Effective Date	Ineffective Date
2	100	Employee 1	$ 25.00	4/1/2021	9/30/2021
3	200	Employee 2	$ 30.00	4/1/2021	6/30/2021
4	200	Employee 2	$ 40.00	7/1/2021	9/30/2021
5	300	Employee 3	$ 20.00	4/1/2021	5/30/2021
6	300	Employee 3	$ 45.00	6/1/2021	6/25/2021
7	300	Employee 3	$ 60.00	6/26/2021	9/30/2021
Rate History

Hi Alansidman,
Thanks so much for your reply. I've used PQ before and continue to use it, but still not an expert with it and learning more every day. I'm going to try this solution (just haven't had the time yet), but I was wondering if there are any simpler solution other than PQ? Do you know if there's a simpler excel function/formula that would also accomplish this?

Reason being is this effort is going to be duplicated on a project by project basis, and I have to pass down this process to multiple folks who doesn't have the PQ knowledge.

I'd greatly appreciate it, thanks!

 

[alansidman]

There may other means to get what you want but not in my skill bank.

 

[Marcelo Branco]

Try

=SUMIFS(Table4[[ Rate ]],Table4[Employee ID],[@[Employee ID]],Table4[Effective Date],"<="&[@Date],Table4[Ineffective Date],">="&[@Date])

M.

 

[egrospeRP]

Try

=SUMIFS(Table4[[ Rate ]],Table4[Employee ID],[@[Employee ID]],Table4[Effective Date],"<="&[@Date],Table4[Ineffective Date],">="&[@Date])

M.

oh, wow, now i feel stupid. That worked!! Thanks!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.