Wolfin2010
New Member
- Joined
- Feb 21, 2015
- Messages
- 23
- Office Version
- 365
- Platform
- Windows
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.