Move time difference to the actual time

[smfhsan2005]

Hello Experts, I have a request seems little easy I did worked on that but not getting the actual result. Your help will be really appreciated. Here is the scenario. Below table has time difference of 2 sec ,I want that value 2  to come at 21:51:47..shown below. same with time value 3.

Date	Time	Time diff
10/21/2019	21:51:45	0
10/21/2019	21:51:46	0
10/21/2019	21:51:47	0
10/21/2019	21:51:48	0
10/21/2019	21:51:49	2
10/21/2019	21:51:50	0
10/21/2019	21:51:51	0
10/21/2019	22:36:23	0
10/21/2019	22:36:24	0
10/21/2019	22:36:25	0
10/21/2019	22:36:26	0
10/21/2019	22:36:27	3
10/21/2019	22:36:28	0
10/21/2019	22:36:29	0
10/21/2019	22:36:30	0
10/21/2019	22:36:31	0
10/21/2019	22:36:32	0
10/21/2019	22:36:33	0

Expected result:

Date	Time	Time diff
10/21/2019	21:51:45	0
10/21/2019	21:51:46	0
10/21/2019	21:51:47	2
10/21/2019	21:51:48	0
10/21/2019	21:51:49	0
10/21/2019	21:51:50	0
10/21/2019	21:51:51	0
10/21/2019	22:36:23	0
10/21/2019	22:36:24	3
10/21/2019	22:36:25	0
10/21/2019	22:36:26	0
10/21/2019	22:36:27	0
10/21/2019	22:36:28	0
10/21/2019	22:36:29	0
10/21/2019	22:36:30	0
10/21/2019	22:36:31	0
10/21/2019	22:36:32	0
10/21/2019	22:36:33	0

Thanks

[JoeS]

Hi @smfhsan2005 

I am not quite sure I understand the question.

How is it that you are calculating the "Time diff" column?

[smfhsan2005]

Hello @JoeS, thats the data I received from my team..Time diff is just the column name. These are dynamic file where if the time diff is 0 there is no change in time in the output but when time diff is greater than 1 it should go to that exact time, in my example for 49 sec it should be 47 sec. Basically we are subtracting the time from the actual time when there is any value in time diff column.

[JoeS]

Thanks for the details.

So when you say in your example it should be 47 and not 49 how is it you know that?

Reason I ask, is that's the logic you'll need to implement?

Is it a case you get a list of changes? Or are they always 2 seconds out?

[GiuseppeC]

Hi @smfhsan2005,

hoping I'm getting what you are trying to achieve, please see if the below helps:

I aggregate Date and Time into one column to create a DateTime type. I then use the DateTimeAdd function in the Formula tool to subtract Time Diff from time (assuming it's always going to be seconds). Finally trim the time only, updating the Time field.

Workflow attached.

Hope this helps!

Giuseppe

[smfhsan2005]

Because these data we receive from one of our tool whose output we receive have more than 30 columns but these 3 columns has an issue, so we are trying to implement the change only in these columns.

2. Or are they always 2 seconds out? It will be any value but that value should be subtracted from the time., I mentioned two example earlier, one is for 2 sec and other is 3 and it will go upto different values.

[smfhsan2005]

Thats great, I am more than half way there, is it possible to get value 2 from row 5 to row 3. so that we can say these time diff started at the that moment 

10/21/2019	21:51:45	0
10/21/2019	21:51:46	0
10/21/2019	21:51:47	0
10/21/2019	21:51:48	0
10/21/2019	21:51:47	2
10/21/2019	21:51:50	0
10/21/2019	21:51:51	0
10/21/2019	22:36:23	0
10/21/2019	22:36:24	0
10/21/2019	22:36:25	0
10/21/2019	22:36:26	0
10/21/2019	22:36:24	3

Expected:

10/21/2019	21:51:45	0
10/21/2019	21:51:46	0
10/21/2019	21:51:47	2
10/21/2019	21:51:48	0
10/21/2019	21:51:47	0
10/21/2019	21:51:50	0
10/21/2019	21:51:51	0
10/21/2019	22:36:23	0
10/21/2019	22:36:24	3
10/21/2019	22:36:25	0
10/21/2019	22:36:26	0
10/21/2019	22:36:24	0

[danilang · Accepted solution]

Hi @smfhsan2005 

Here's a workflow that does it all

The filter finds the rows with non-zero time differences.  The lower formula figures out which rows to update and these are joined.  The top filter set the time diff to 0 on the old rows.  All of these, the 2 new, the 2 old and the remainder are unioned together and sorted by date time

 

Dan

[JoeS]

Can always rely on @danilang !

[smfhsan2005]

Wow...Thats what I need..Thanks Dan, you saved me:)..Thanks