Rounding down Date-Time to the quarter hour level

[cdunn]

Hi,

I am looking to round my date time field down to the quarter hour (15 min level). An example of what I'm looking to achieve is below:

Current Date/Time
2015-03-06 18:18:16
2015-03-06 18:48:21
2015-03-06 19:03:22
2015-03-06 19:18:10
2015-03-06 21:33:13

would translate to...

QH Date/Time

2015-03-06 18:15:00
2015-03-06 18:45:00
2015-03-06 19:00:00
2015-03-06 19:15:00
2015-03-06 21:30:00

Does anyone have an easy fix for this? I was thinking of parsing the minute field out, and building a formula to determine the QH minute field each instance would be categorized under, and then concatenating the data together again. But I am guessing there is an easier way to handle this.

Thanks!

Carmin

[jdunkerley79 · Accepted solution]

The following will do what you need:

LEFT([Current Date/Time],14) +
PadLeft(ToString(Floor(ToNumber(SUBSTRING([Current Date/Time],14,2)) / 15) * 15, 0), 2, '0')
+ ':00'

Basically treating the datetime as a string and working on just the minute part.

The first line breaks the date and hour part off.

The last line adds back a second

The middle line breaks out the minutes, and rounds them down.

ToNumber(SUBSTRING(SUBSTRING([Current Date/Time],14,2))

 Gets the minutes parts as a double.

Floor(...) / 15) * 15

rounds this down to the quarter of the hour.

Finally

PadLeft(ToString(..., 0), 2, '0')

converts back to a string and pads with 0 if needed.

Attached a sample.

[cdunn]

This is perfect, thank you!