Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Rounding down Date-Time to the quarter hour level

cdunn
5 - Atom

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

2 REPLIES 2
jdunkerley79
ACE Emeritus
ACE Emeritus

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
5 - Atom

This is perfect, thank you!

Labels