Trying to correct rate base on date.

[Wolfin2010]

Book1
	A	B	C	D	E	F	G
1	Date	Start Time	Finish Time	Total Hours		Amount	HST
2	20-Oct-19	7:00	8:00	1:00
3	21-Oct-19
4	22-Oct-19
5	23-Oct-19
6	24-Oct-19
7	25-Oct-19			1:00
8	26-Oct-19					$22.60	$2.94
9	01-Nov-19	7:00	8:00	1:00
10	02-Nov-19
11	03-Nov-19
12	04-Nov-19
13	05-Nov-19
14	06-Nov-19			1:00
15	07-Nov-19					$22.60	$2.94
16	15-Nov-19	7:00	8:00	1:00
17	16-Nov-19
18	17-Nov-19
19	18-Nov-19
20	19-Nov-19
21	20-Nov-19			1:00
22	21-Nov-19					$22.60	$2.94
23	01-Dec-19	7:00	8:00	1:00
24	02-Dec-19
25	03-Dec-19
26	04-Dec-19
27	05-Dec-19
28	06-Dec-19			1:00
29	07-Dec-19					$22.60	$2.94
Sheet1

Cell Formulas
Range	Formula
D2:D6, D23:D27, D16:D20, D9:D13	D2	=IF(B2="","",IF(C2="","",IF(C2<B2,"Wrong Time",C2-B2)))
D7, D28, D21, D14	D7	=IF(SUM(D2:D6)=0,"",SUM(D2:D6))
A3:A8, A24:A29, A17:A22, A10:A15	A3	=A2+1
F8, F29, F22, F15	F8	=IF(D7="","",D7*LOOKUP(2,1/(Rates!A:A<>""),Rates!B:B)*1.13*24)
G8, G29, G22, G15	G8	=IF(F8="","",F8*1.13-F8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D24:D27	Cell	does not contain a blank value	text	NO
E29	Cell	does not contain a blank value	text	NO
E28	Cell	does not contain a blank value	text	NO
G29	Cell	does not contain a blank value	text	NO
F29	Cell	does not contain a blank value	text	NO
D23	Cell	does not contain a blank value	text	NO
D28	Cell	does not contain a blank value	text	NO
D17:D20	Cell	does not contain a blank value	text	NO
E22	Cell	does not contain a blank value	text	NO
E21	Cell	does not contain a blank value	text	NO
G22	Cell	does not contain a blank value	text	NO
F22	Cell	does not contain a blank value	text	NO
D16	Cell	does not contain a blank value	text	NO
D21	Cell	does not contain a blank value	text	NO
D10:D13	Cell	does not contain a blank value	text	NO
E15	Cell	does not contain a blank value	text	NO
E14	Cell	does not contain a blank value	text	NO
G15	Cell	does not contain a blank value	text	NO
F15	Cell	does not contain a blank value	text	NO
D9	Cell	does not contain a blank value	text	NO
D14	Cell	does not contain a blank value	text	NO
D3:D6	Cell	does not contain a blank value	text	NO
E8	Cell	does not contain a blank value	text	NO
E7	Cell	does not contain a blank value	text	NO
G8	Cell	does not contain a blank value	text	NO
F8	Cell	does not contain a blank value	text	NO
D2	Cell	does not contain a blank value	text	NO
D7	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F8:G8	Any value
D2:D7	Any value
F15:G15	Any value
D9:D14	Any value
F22:G22	Any value
D16:D21	Any value
F29:G29	Any value
D23:D28	Any value

Book1
	A	B
1	Date	Hour Rate
2	15-Oct-19	$18.00
3	01-Nov-19	$18.25
4	15-Nov-19	$18.75
5	01-Dec-19	$19.00
6	15-Dec-19	$19.50
7	01-Jan-20	$20.00
Rates

I'm trying to figure out if there is a way for me to use range of date to get the rate; so I don't have to do it manually.

What I'm trying to do is F7 to look at the date on A Column (Sheet1) and then check the date (on Rates sheet) then pick the rate which is in the range of the date.
Any idea and advise will be greatly appreciated.

 

[Peter_SSs]

What I'm trying to do is F7 to look at the date on A Column (Sheet1) and then check the date (on Rates sheet) then pick the rate which is in the range of the date.

You are not saying what the expected result(s) are but does this point you in the right direction?

In F7: =IF(D7="","",VLOOKUP(A7,Rates!$A$1:$B$1000,2))

 

[Wolfin2010]

You are not saying what the expected result(s) are but does this point you in the right direction?

In F7: =IF(D7="","",VLOOKUP(A7,Rates!$A$1:$B$1000,2))

You sir are a magician; Thank you so much.

 

[Wolfin2010]

Is there a way to get the answer on other cell?

Book1
	A	B	C	D	E
1	1:00	30		0:30
2			Trying to get the answer at C1
3
4
5
Sheet2

Cell Formulas
Range	Formula
D1	D1	=IF(SEARCH("30",B1),VALUE(A1)-"0:30","No")

 

[Peter_SSs]

You sir are a magician; Thank you so much.

You're welcome.

Is there a way to get the answer on other cell?

I don't understand what your requirement is.

 

[Wolfin2010]

You're welcome.


I don't understand what your requirement is.

What I need to do is that I need the answer on C1 but I can't put the formula there; that's why is there a formula which I could put at D1 and it gives me the answer on C1?

 

[Peter_SSs]

A formula like that cannot put its result in another cell. If you want the result in C1, why can't you just put the formula in C1 instead of in D1?