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?

score 4 · Accepted Answer · edited Nov 24 '15 at 20:08

4

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:

edited Nov 24 '15 at 20:08

answered Nov 24 '15 at 11:37

Inigo

8,110
18
62
110

score 3 · Answer 2 · edited Jun 15 '17 at 07:20

3

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

edited Jun 15 '17 at 07:20

SteveC

15,808
23
102
173

answered Nov 28 '16 at 12:08

Ted

3,985
1
20
33

Alternative to settings Format to Time would be to wrap in Text function such as `=TEXT(FLOOR(A1, 1/96), 'h:mm:ss')` – tzvi Sep 03 '17 at 23:30

Round down to nearest quarter hour

2 Answers2