How to add or subtract Time
- 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
Hello!
I have recently started using Alteryx. I have a question. I want to subtract just time to get an hour difference.
e.g
I want to subtract 17:29 - 04:00. How can I do that?
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If you don't need it to be converted to a DateTime format, then the following formula should work:
ToString(ToNumber(Left("17:49",2))-4) + TrimLeft("17:49",Left("17:49",2))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey T_Willins,
Thank you for your response. This works but, I am trying to subtract time for multiple rows in if statement.
I have a columns named [Calculation] and [DateTime_Out]. So, if calculation = "TRUE" then for that row I want to subtract time from the previous row. the data type is Time for column DateTime_Out and i am getting error
"Formula: invalid type in subtraction operator" when I am trying to use the below logic.
if [Calculation] = "TRUE"
then [DateTime_Out]-[Row-1:DateTime_Out] else [DateTime_Out] endif
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@adneyadeodhar I think the formula you are looking for is:
if [Calculation] = "TRUE" then datetimediff([DateTime_Out],[Row-1:DateTime_Out], 'minutes') then else [DateTime_Out] endif... that should do the trick.
Alteryx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @DiganP
Thank you for your response. I tried the solution which you suggested but, it is not working. I have attached the screenshot for reference.
In the screenshot, Adneya is O/p column where your solution should return 02:43:00 for the highlighted row. But, it is returning 00:00:00
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @adneyadeodhar,
It looks like the formula and field formats are not consistent. The 'DateTimeDiff" formula returns a number format, not a DateTime format, so the formula you are using on the highlighted row would return '163' as a result, not 02:43:00. If you need the result in that format, you will need to change the minutes to hours (see attached workflow for example).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@T_Willins
This worked! Thank you.
