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

Formula to round down AND subtract 30 minutes

MCDR929
8 - Asteroid

Hello all,

 

I have to create a start datetime and end datetime to narrow my dynamic input.

 

For this workflow, I'm looking at 30 minute increments.  The report will run at, say, 5 minutes past each half hour.  I need my END datetime to be rounded down to the nearest half hour, and my START datetime to be 30 minutes before that (so, encompassing all data from that 30 minute period of time).  

 

I'm still learning my way around datetime formulas, and would appreciate a nudge in the right direction. Thank you!

3 REPLIES 3
BrandonB
Alteryx
Alteryx

Workflow attached!

 

date time.png

BrandonB
Alteryx
Alteryx

Formula for rounding down to the nearest half hour: 

 

IF ToNumber(DateTimeFormat([Now],"%M"))<30
THEN DateTimeFormat([Now],"%Y-%m-%d %I:00:00")
ELSE DateTimeFormat([Now],"%Y-%m-%d %I:30:00")
ENDIF

 

This pulls the minutes out of the current date time and says if that number is less than 30, set the minutes and seconds to 0, because we want to round all the way down to the bottom of the hour. Otherwise, if the minutes are greater than 30, we want to round down to 30 minutes. 

 

 

The second formula for getting the start time is a bit easier:

DateTimeAdd([Round down nearest half hour],-30,"minutes") 

 

This uses the function datetimeadd to subtract 30 minutes from the date time field specified. 

 

MCDR929
8 - Asteroid

Perfect - thank you!  I really appreciate the additional explanation. 

Labels