Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

How to add or subtract minutes from a time?

ddiesel
13 - Pulsar
13 - Pulsar

How to I add or subtract minutes from a time field? 

 

In this case, I need to add 30 minutes to each of my "Start Times" to get the "End Times".

 

DateTimeAdd([Start Time],+30,"minutes") gives conversion error: DATETIMEADD: "04:00:00" is not a valid DateTime.

 

I can think of a couple workarounds but is there an easy way to do this?

 

Sample workflow:

 

14 REPLIES 14
ddiesel
13 - Pulsar
13 - Pulsar

... and thanks @KevinP! You both had the answer! 

jt_edin
8 - Asteroid

This is brilliant, thank you. But why does the date component need a space before the end quote? 

"2017-01-01 "

It doesn't parse without the space, yet I don't understand how I would've figured that out if it wasn't for @MarqueeCrew's brilliant suggestion. Any ideas what the space is for? Thanks 

KevinP
Alteryx Alumni (Retired)

@jt_edin The space is to ensure there is separation between the date and the time when appending the string values. Let's consider the formula both @MarqueeCrew and I previously posted:

 

DateTimeAdd("1970-01-01 "+[Start Time],30,"minutes")

The section '"1970-01-01 "+[Start Time]' takes the value of the field Start Time and appends it to the string '1970-01-01 '. Assuming Start Time is set to 12:00:00 this results in a value of '1970-01-01 12:00:00'. If the space wasn't present the date and time would end up run together like this '1970-01-0112:00:00'. This value without the space wouldn't be treated as a valid datetime by the DateTimeAdd() function. You can see/test this yourself in a formula tool with the following formulas

 

Formula with space:

"1970-01-01 "+[Start Time]

Formula without space:

"1970-01-01"+[Start Time]

Alternatively you could add the space separately but it would require a longer formula to get the same result as seen below:

DateTimeAdd("1970-01-01"+" "+[Start Time],30,"minutes")

 

 

amitsrivastava
6 - Meteoroid
 
MohithRai
8 - Asteroid

 

Labels