Subtracting an Hour from Time field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Help
- Time Series
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You could use a DateTimeFormat() function
DateTimeFormat([time difference],'%I:%M:%S %p')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can find out about date time formats here:
https://help.alteryx.com/20213/designer/datetime-functions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you 🙂 Super helpful
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
