Indentify the closest time

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.

Developer

Data Investigation

Accepted answers

caltang

@Ronal_bal Like so:

For Ronal.yxzp

Qiu

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

1024-Ronal_bal.yxmd

All comments

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.

Test.xlsx

caltang

@Ronal_bal Like so:

For Ronal.yxzp

Qiu

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

1024-Ronal_bal.yxmd

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.

asn-2345.png

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703