How to add or subtract Time

[adneyadeodhar]

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? 

[T_Willins]

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))

[adneyadeodhar]

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

[DiganP · Accepted solution]

@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. 

[adneyadeodhar]

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

 

[T_Willins · Accepted solution]

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).

[adneyadeodhar]

@T_Willins

This worked! Thank you.