how about this:
IF
Tonumber(Substring([Time],19))-ToNumber(substring([row-1:Time],19))>=0.5
THEN
datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')+1
ELSE
datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')
ENDIF
So we add 1 second if the millisecond difference is more than 0.5
Hope that helps,
Ollie
Hello @Alteryx_Geek ,
Maybe with a combination of some MultiRow and Formula transformations you can get what you need. Here you have an example that might help you:
Try to calculate Time Differences betweeen rows and then make the transformations in order to get days, minutes, secs and millisecs.
Check the workflow that I attached.
Hope it helps.
Andreina
Hey @Alteryx_Geek
Apologies, I'd misread your actual question. This I think gives what you want:
ROUND(
datetimediff(LEFT([time],19),LEFT([row-1:time],19),'seconds')
+
tonumber(substring([time],19))-tonumber(substring([row-1:time],19))
,0.1)It outputs the number of seconds between a row and the row above to 0.1 of a second.
Hope that helps,
Ollie
@OllieClarke - Apologize for late reply. Thanks for sharing Ollie. looks like your output is coming along but when you have answer in msec, its rounding off the number.
As highlighted below, the output should have been "0.11" instead of 1
