VLOOKUP Problems

[Lee 1973]

Unfortunalty I am unable to upload an example, I shall though try to explain my situation I am using a VLOOKUP formula to determine from the hours worked what breaks will be deducted from the total hours for example 4 hours = 0.25, 5 hours = 0.25, 6 hours = 0.5, 7 hours = 0.75, 8 hours = 1, 9 hours = 1.5 everything is fine when I use a seperate table (A1:B19) A column defining the hours column defining the breaks, I use a seperate table with hour started in column D and hour finished in column E the total hours are calculated in Column F using a simple =SUM(F2-E2)*24, Column G then calculates the given break time from the table earlier explained using =VLOOKUP(F2,$A$2:$B$19,2,0) everthing runs fine up until I get to 6.5 hours and 7 hours I get the #N/A error when in fact the 0.5 should show on the 6.5 and 0.75 on the 7 hours?

I hope that I have explained this well and that someone can help fix this issue, Many Thanks In advance and happy holidays

 

[FDibbins]

the 0 in your vlookup()...
=VLOOKUP(F2,$A$2:$B$19,2,0)
is looking for an exact match - F2 may not be an exact match, try using 1, instead of 0

Also, you dont need to use SUM here...
=SUM(F2-E2)*24
You can just use =(F2-E2)*24

 

[Lee 1973]

Thanks Ford, however when I use the =VLOOKUP(F2,$A$2:$B$19,2,1) version the 7 hours worked only calcualtes a 0.5 break not the 0.75 ? Very frustrating...

 

[Lee 1973]

START	FINISH	HOURS @ WORK	ALLOCATED BREAKS	TOTAL HOURS
6:00 AM	10:00 AM	4	0.25	3.75
6:00 AM	10:30 AM	4.5	0.25	4.25
6:00 AM	11:00 AM	5	0.25	4.75
6:00 AM	11:30 AM	5.5	0.25	5.25
6:00 AM	12:00 PM	6	0.5	5.5
6:00 AM	12:30 PM	6.5	0.5	6
6:00 AM	1:00 PM	7	0.5	6.5
6:00 AM	1:30 PM	7.5	0.75	6.75
6:00 AM	2:00 PM	8	1	7
6:00 AM	2:30 PM	8.5	1	7.5
6:00 AM	3:00 PM	9	1.5	7.5
6:00 AM	3:30 PM	9.5	1.5	8
6:00 AM	4:00 PM	10	1.5	8.5
6:00 AM	4:30 PM	10.5	1.5	9
6:00 AM	5:00 PM	11	1.5	9.5
6:00 AM	5:30 PM	11.5	1.5	10

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[FDibbins]

Based on your sample data...

	A​	B​	C​	D​	E​
1​	START	FINISH	HOURS @ WORK	ALLOCATED BREAKS	TOTAL HOURS
2​	6:00 AM	10:00 AM	4:00​	0.25	3:45​
3​	6:00 AM	10:30 AM	4:30​	0.25	4:15​
4​	6:00 AM	11:00 AM	5:00​	0.25	4:45​
5​	6:00 AM	11:30 AM	5:30​	0.25	5:15​
6​	6:00 AM	12:00 PM	6:00​	0.05	5:57​
7​	6:00 AM	12:30 PM	6:30​	0.05	6:27​
8​	6:00 AM	1:00 PM	7:00​	0.05	6:57​
9​	6:00 AM	1:30 PM	7:30​	0.75	6:45​
10​	6:00 AM	2:00 PM	8:00​	1	7:00​
11​	6:00 AM	2:30 PM	8:30​	1	7:30​
12​	6:00 AM	3:00 PM	9:00​	1.5	7:30​
13​	6:00 AM	3:30 PM	9:30​	1.5	8:00​
14​	6:00 AM	4:00 PM	10:00​	1.5	8:30​
15​	6:00 AM	4:30 PM	10:30​	1.5	9:00​
16​	6:00 AM	5:00 PM	11:00​	1.5	9:30​
17​	6:00 AM	5:30 PM	11:30​	1.5	10:00​
18​
19​
20​	4:00​	0.25
21​	5:00​	0.25
22​	6:00​	0.05
23​	7:00​	0.75
24​	8:00​	1.00
25​	9:00​	1.50

C2=B2-A2
D2=VLOOKUP(C2,$A$20:$B$25,2,1)
E2=C2-(D2/24)
all copied down

 

[Lee 1973]

Thanks Ford, That works really well however I need to be able to have a total number of hours at the end of the week/month as the above being an example the total number of hours should be close to 109.5 hours rather than 13:45 hours is there another little trick to combat this ? I really appreciate your help in this matter

 

[FDibbins]

OK, same table, but I added a column for date (your sample did not show any dates?

	A​	B​	C​	D​	E​	F​
1​	Date	START	FINISH	HOURS WORK	BREAKS	Total
2​	2-Dec​	6:00 AM	10:00 AM	4:00​	0.25	3:45​
3​	2-Dec​	6:00 AM	10:30 AM	4:30​	0.25	4:15​
4​	2-Dec​	6:00 AM	11:00 AM	5:00​	0.25	4:45​
5​	2-Dec​	6:00 AM	11:30 AM	5:30​	0.25	5:15​
6​	2-Dec​	6:00 AM	12:00 PM	6:00​	0.05	5:57​
7​	2-Dec​	6:00 AM	12:30 PM	6:30​	0.05	6:27​
8​	2-Dec​	6:00 AM	1:00 PM	7:00​	0.05	6:57​
9​	3-Dec​	6:00 AM	1:30 PM	7:30​	0.75	6:45​
10​	3-Dec​	6:00 AM	2:00 PM	8:00​	1	7:00​
11​	3-Dec​	6:00 AM	2:30 PM	8:30​	1	7:30​
12​	3-Dec​	6:00 AM	3:00 PM	9:00​	1.5	7:30​
13​	3-Dec​	6:00 AM	3:30 PM	9:30​	1.5	8:00​
14​	4-Dec​	6:00 AM	4:00 PM	10:00​	1.5	8:30​
15​	4-Dec​	6:00 AM	4:30 PM	10:30​	1.5	9:00​
16​	4-Dec​	6:00 AM	5:00 PM	11:00​	1.5	9:30​
17​	4-Dec​	6:00 AM	5:30 PM	11:30​	1.5	10:00​
18​
19​
20​		4:00​	0.25	2-Dec	37:21:00​
21​		5:00​	0.25	3-Dec	36:45:00​
22​		6:00​	0.05	4-Dec	37:00:00​
23​		7:00​	0.75	Week	111:06:00​
24​		8:00​	1
25​		9:00​	1.5

E20=SUMIF($A$2:$A$17,D20,$F$2:$F$17)
copied down. This will give you a total for a specific date

E23=SUMIFS($F$2:$F$17,$A$2:$A$17,">="&D20,$A$2:$A$17,"<="&$D$22)
This will give you a total between those 2 dates...you could put a different start and end date in different cells, to make this more dynamic
In the above (new) formulas, format them to Custom [h]:mm:ss

 

[Martin2404]

Try taking out the zero at the end of the vlookup formula.
=VLOOKUP(F2,$A$2:$B$19,2)

 

[Martin2404]

Also try rounding your time calculation.
=ROUND(SUM(E2-D2)*24,2)

 

[Lee 1973]

Thank You Martin, I did use your suggestion plus I also used another way of doing which now works brilliantly, let me again thank you very much I wouldn't have gotten there without your valuable information

Thanks

How do I mark this thread as solved?