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!
Solved! Go to Solution.
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.
Perfect - thank you! I really appreciate the additional explanation.