Indentify the closest time

[Ronal_bal]

Hi,

I need to identify the closest time when field A= 1, I need to identify the closest time in the following rows. I need to continue until there is another A=1.

Attached the example,

For Group 1, Whenever A=1, Then Compare its Value from C to the closest in the same column untill there another A=1.If there is non found return 0 on Column D or add the closest time in column D.

The same process goes on.

[caltang]

It’s great you included details… but no JPG of data please. Please provide relevant data to this use case, and kindly provide your criteria in as much detail as possible. If you have a workflow built halfway, kindly export that over as well.

[Ronal_bal]

@caltang attached the example excel sheet.

[caltang · Accepted solution]

@Ronal_bal Like so:

[Qiu · Accepted solution]

@Ronal_bal 
Somehow it gets bigger😂
Basically it is to seregate the groups based on Group and A.

Then do a joint and find the absolute DateTimeDiff.

[Ronal_bal]

Thank you for providing the logic. But now there is a slight change in selecting the closest time. 

i.e. The closest time should be within 30 Minute range (+30 and -30 minutes)

The value for column C when A=1 should be closer to +30 or -30 minutes.

If the Value when A=1, the timings within 30 min(+) or 30 Min(-) then flag okay

if the value when A=1, for the timing which are greater than 30 minute(+) and not less then 30 min (-) intreval then delayed

if the value A=1, for the timing which are greater than 30 minute (-) and not less then then 30 min (+) intreval then ahead.

@Qiu @caltang 

There can be 3 or more timings which can be within the 30 minute(+,-) range. 

[caltang]

Hi @Ronal_bal - your original request has already been fulfilled. What you mentioned is a new request. I do not think that is appropriate for you to add on like this.

Please write a new forum post to resolve your need there. In addition, as @Qiu and I have provided you the solution to your initial query, kindly close this post by marking both our solutions as accepted solutions. 

Thanks.

[caltang]

Hi @Ronal_bal - small ask, my solution did resolve your request as well. Kindly mark it as an accepted solution to help others find the solution better. Thanks.