Rounding minutes

[Weasel]

I'm working on creating a time sheet in which I enter a time and it then spits out a decimal that the time corresponds to (i.e. someone worked 8:30 = 8.50). So in cell A1 I have the hours worked and in cell A2 I have the formula A2*24.

What I now need to do is figure out a way to round the minutes based on the following criteria:

If minutes are between 0-7 then round down to the hour (i.e. 7:06 = 7.0)
If minutes are between 8-22 then round to .25 of an hour (i.e. 5:12 = 5.25)
If minutes are between 23-42 then round to .50 of an hour (i.e. 9:32 = 9.50)
If minutes are between 43-52 then round to .75 of an hour (i.e. 3:49 = 3.75)
If minutes are between 52-59 then round up to the hour (i.e. 10:54 = 11.00)

Thanks in advance for any help

[Weasel]

No one can help on this?

[arthurbr]

You can find some help here :
http://www.cpearson.com/excel/lookups.htm#ClosestMatch

[daddylonglegs]

or here

http://www.mrexcel.com/board2/viewtopic.php?t=252345

[tmwells]

Here you go.

Place the "Time in" in cell B2 and "Time out" in cell B3. Both cells are rounded based on your criteria and subtracted from each other to get total time worked in cell B4. It is important that you place a validation on the input cells so that the user is required to put am or pm.

Tom

[Dav]

=ROUND(A1*24*4,0)/4

Regards

Dav