Excel Formula request

Question

I have several people that worked a certain amount of hours and drove a certain amount of miles. If they drove 0-19 miles they get to add no time onto their hours. If they drove 20-29 miles they get .5 hours added to their volunteer time, 30-39 miles gets 1 hour, etc.. I want to find a formula that will add the additional time based off of the miles to the hours. I also am using the formula of =TEXT(cell-cell, "H") for the hours calculation. Is this even possible or will I need to always calculate it by hand?

Answer 1

Hi,

Please respond if I could be of further support.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Hi leebrashi

To calculate the hours to add from distance travel, if I have understood your data, this is the formula you need, you will need to replace the A1sell reference with the correct cell reference

=IF(A1<19,0,CEILING((((A1-19)/10)*0.5),0.5))

Answer 3

Hi,

Please check whether the following solution is helpful:

To calculate Total Hours, formula in cell G2 will be: =E2+VLOOKUP(F2,$J$3:$K$11,2,TRUE)

To only calculate the extra hours, the formula in cell G2 will be:

=VLOOKUP(F2,$J$3:$K$11,2,TRUE)

Note

1) In the above formula, please change cell references/ranges to suit Your requirement.

2) Please drag the formula down to as many rows as required.

Please respond if You require further support. I will try My Best to be of help.

If I was able to help You, please mark My response as "answer" and "helpful".

Thank You!

Answer 4

Here is my sample. Names are made up.

Date	Name	Start time	End time	Hours	Miles
1-Oct	Joe Hansen	8:00	15:00	7:00	22		Hours	Mileage
3-Oct	Mary Shirtz	7:30	12:00	4:30	45		0-19	No added time
4-Oct	Carol Mybring	14:30	16:00	1:30	132		20-29	30 min
5-Oct	Eddie Johnson	10:00	17:20	7:20	30		30-39	45 min
5-Oct	Fisher Carter	10:15	15:40	5:25	14		40-40	1 hour
5-Oct	Jenelle Riley	9:55	11:30	1:35	9		50-59	1.5 hours
							60-69	2 hours
							70-79	2.5 hours
							80-89	3 hours
							90-100	3.5 hours

Answer 5

Hi leebrashi

I am Dave, I will help you with this.

I need to see a sampling of your data, which includes the row and column headers so I can provide you with the appropriate formula.