Subtracting an Hour from Time field

[cayladuplooy]

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

[DavidP · Accepted solution]

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

[cayladuplooy]

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]

You could use a DateTimeFormat() function

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

[DavidP]

You can find out about date time formats here:

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

[cayladuplooy]

Thank you 🙂 Super helpful

[cayladuplooy]

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]

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