Round time off to nearest 15 minutes and then convert format

[Purple_Haze]

I'm struggling to work out a formula to round off time to the nearest 15 minutes.

Basically the numbers are timesheet entries and they need to be rounded in the following format:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Current Format[/TD]
[TD]Desired Output[/TD]
[/TR]
[TR]
[TD]02:19:04[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]01:37:00[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]03:55:23[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I've been able to round it in decimal points, but just can't get the formula to work perfectly.

This is what I have done...

First calculation:
=HOUR(R[4]C[-3]) + MINUTE(R[4]C[-3]) /60 +SECOND(R[4]C[-3]) / 3600
Second calculation:
=FLOOR(RC[-2],0.25)

It's not working, so I must be completely wrong. Any help would be much appreciated!

 

[Fluff]

Do you want to round up, round down, or round to the nearest 0.25?

 

[Purple_Haze]

Do you want to round up, round down, or round to the nearest 0.25?

Looking to round it to the nearest 0.25

 

[Fluff]

In that case, how about

Book1
	A	B
2	02:19:04	2.25
3	01:37:00	1.50
4	03:55:23	4.00
List

Cell Formulas
Range	Formula
B2	=MROUND(A2*24,0.25)

 

[Purple_Haze]

In that case, how about

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]02:19:04[/TD]
[TD="align: right"]2.25[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01:37:00[/TD]
[TD="align: right"]1.50[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]03:55:23[/TD]
[TD="align: right"]4.00[/TD]

</tbody>

List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MROUND(A2*24,0.25)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked perfectly! Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback