Alteryx Designer Desktop Discussions

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

Subtracting an Hour from Time field

cayladuplooy
7 - Meteor

I am trying to subtract 1 hour from my time field in Alteryx. This is not datetime, it is only time e.g. 00:00:00. The DatetimeAdd function won't work because it is a time field type. Is there a formula I can use?

 

Thank you

7 REPLIES 7
DavidP
17 - Castor
17 - Castor

Hi @cayladuplooy 

 

I don't think there is a timeadd function. 

 

The first option that comes to mind is to add a static date in order to use DateTimeAdd()

 

Something like Replace(DatetimeAdd('2021-01-01 '+[time field],-1,'hours'),'2021-01-01 ','')

 

I use the Replace function to remove the static date afterwards

 

DavidP_0-1640045104765.png

 

cayladuplooy
7 - Meteor

Thank you David, that works. It does give it to me in 24 hour time which I think is the default for Alteryx. If I did not want it in 24 hour is there a work around?

 

 

DavidP
17 - Castor
17 - Castor

You could use a DateTimeFormat() function

 

DateTimeFormat([time difference],'%I:%M:%S %p')

 

DavidP_0-1640046593366.png

 

DavidP
17 - Castor
17 - Castor

You can find out about date time formats here:

 

https://help.alteryx.com/20213/designer/datetime-functions

 

cayladuplooy
7 - Meteor

Thank you 🙂 Super helpful

cayladuplooy
7 - Meteor

It turns out I actually want to just subtract an hour from the time taken. So if the time taken to do a task is 01:42:30 then I would want to subtract 1 hour and make it 00:42:30. Do you know how to do that?

 

DavidP
17 - Castor
17 - Castor

Hi @cayladuplooy 

 

The formula in my first reply would still work for this.

 

Replace(DatetimeAdd('2021-01-01 '+[time field],-1,'hours'),'2021-01-01 ','')

 

The only exception is if the time taken is less than one hour. So perhaps wrap the formula in an if statement like:

 

if tonumber(left([time field],2))>0 then 

   Replace(DatetimeAdd('2021-01-01 '+[time field],-1,'hours'),'2021-01-01 ','')

else

  'error message'

endif

Labels
Top Solution Authors