Round down to nearest quarter hour

Question

I have a timesheet which I use for billing clients. It records entries from Toggl as hour fractions, ie 35 minutes is recorded as 0.58. I want to round this number down to the nearest 0.25, ie. the nearest quarter hour. Is this possible in Google Sheets?

Answer 1

After a bit more reading, the function I was looking for is MROUND, to round to the nearest given fraction. eg.

=MROUND(A1,0.25)

The crucial step required is to add or substract half a step depending on whether you want it round up or down, eg.

Up: =MROUND(A1+0.125;0.25)
Down: =MROUND(A1-0.125;0.25)

Added screenshots for clarity:

Answer 2

After many tries with ROUND and MROUND I could only get it to work with FLOOR (rounds down) and CEILING (rounds up).

The following example rounds down to the nearest half hour:

=FLOOR(A1, 1/96)

1/24 for an hour, 1/48 for half an hour etc.

The last thing to do is change the Format of the field to time to get a proper time format:

00:00:00