Round Time Down

[tchovanec]

I am looking for a way to always round time down to the nearest :45. For example if the time is 12:38, it would round down to 11:45. Any help would be greatly appreciated. Thank you.

[m_woolf · Accepted solution]

Add 15 minutes, round down to the hour, and subtract 15 minutes?

[swuehl]

Use floor with an offset:

=interval(floor(time#('12:38','hh:mm'), Interval#('1:00','h:mm'), Interval#('00:45','hh:mm')))

[sunny_talwar]

Great idea mike woolf, but I was trying to think of scenarios where it may not work. 12:48 may round down to 11:45 also, right?

[m_woolf]

12:48 + 15 minutes = 13:03

13:03 rounds down to 13:00

13:00 - 15 minutes = 12:45

I would look at swuehl's reply. He knows everything.

[sunny_talwar]

Genius

[tchovanec]

This also works perfect