Hello all, Been trying to figure out the best way to approach this - I am trying to get the time difference (minutes) between end time of each event A row compared to the start of event B within the same day & for the same person. Below is some dummy data with an added column of what I'd hope to get: entryPersonDateStartEndEventDesired Resulthow calculated1X3/1/233:15:35 PM3:30:00 PMA200 minsdiff of end time entry 1 and start time of entry 3 (event B)2X3/1/234:50:45 PM5:05:00 PMA105 minsTime diff of entry 2 end time and entry 3 start time3X3/1/236:50:00 PM7:10:00 PMBN/AN/A4X3/1/237:30:00 PM9:00:00 PMA20 minsTime diff of start time entry 4 and end time of entry 35X3/2/234:00:00 PM4:30:00 PMA30 minsTime diff of end time entry 5 and start time of entry 66X3/2/235:00:00 PM5:40:00 PMBN/AN/A7X3/2/237:00:00 PM7:20:00 PMA80 minsTime diff of start time of entry 7 and end time entry 68Y3/1/231:00:00 PM1:31:00 PMA209 minsTime diff of end time entry 8 and start time of entry 99Y3/1/235:00:00 PM7:00:00 PMBN/AN/A10Y3/1/237:01:00 PM7:30:00 PMA29 minsTime diff of start time entry 10 and end time entry 911 Y 3/1/238:30:00 PM10:00:00 PMA90 minsTime diff of start time entry 11 and end time entry 9

Time Difference between different rows given specific criteria

Hello all,

Been trying to figure out the best way to approach this - I am trying to get the time difference (minutes) between end time of each event A row compared to the start of event B within the same day & for the same person. Below is some dummy data with an added column of what I'd hope to get:

entry	Person	Date	Start	End	Event	Desired Result	how calculated
1	X	3/1/23	3:15:35 PM	3:30:00 PM	A	200 mins	diff of end time entry 1 and start time of entry 3 (event
2	X	3/1/23	4:50:45 PM	5:05:00 PM	A	105 mins	Time diff of entry 2 end time and entry 3 start time
3	X	3/1/23	6:50:00 PM	7:10:00 PM	B	N/A	N/A
4	X	3/1/23	7:30:00 PM	9:00:00 PM	A	20 mins	Time diff of start time entry 4 and end time of entry 3
5	X	3/2/23	4:00:00 PM	4:30:00 PM	A	30 mins	Time diff of end time entry 5 and start time of entry 6
6	X	3/2/23	5:00:00 PM	5:40:00 PM	B	N/A	N/A
7	X	3/2/23	7:00:00 PM	7:20:00 PM	A	80 mins	Time diff of start time of entry 7 and end time entry 6
8	Y	3/1/23	1:00:00 PM	1:31:00 PM	A	209 mins	Time diff of end time entry 8 and start time of entry 9
9	Y	3/1/23	5:00:00 PM	7:00:00 PM	B	N/A	N/A
10	Y	3/1/23	7:01:00 PM	7:30:00 PM	A	29 mins	Time diff of start time entry 10 and end time entry 9
11	Y	3/1/23	8:30:00 PM	10:00:00 PM	A	90 mins	Time diff of start time entry 11 and end time entry 9

Date Time

Help

Preparation

Accepted answers

martinding

Hi @Draymora,

I think this is matches the expected logic:

But note for the last 2 rows, I think the original Desired Output contains errors.

WARNING: This is not a pretty workflow, and I hope someone can make it more efficient, but it should be the logic you are looking for.

Time Diff Example.yxmd

All comments

martinding

Hi @Draymora,

How is this row calculated?

3/2/23

7:00:00 PM

7:20:00 PM

80 mins

There doesn't seem to be a corresponding event B that starts after the end time of A.

And similarly:

3/1/23

7:30:00 PM

9:00:00 PM

20 mins

Y	3/1/23	7:01:00 PM	7:30:00 PM	A	30 mins
Y	3/1/23	8:30:00 PM	10:00:00 PM	A	60 mins

Draymora

The 80 mins is the 5:40 pm end time of event B to the time the event A starts at 7pm on 3/2 for person X. Trying to get the desired calc

Draymora

It’s the time difference of the end event B to the start of each A if B is before A, the end of event A to the start of event B if A is before B on same day. Event As could be before or after B. Apologies my desired calc I was trying to mental math the difference. There’s no formula in alteryx currently I’m trying to get the calc

Draymora

The 80 mins is the 5:40 pm end time of event B to the time the event A starts at 7pm on 3/2 for person X. Trying to get the desired calc

Qiu

@Draymora
I am also a bit confused with the logic here.
Maybe you can add one column to demonstrate how the "Desired Result" is calculated for each row.

Draymora

Added additional columns with details

martinding

Hi @Draymora,

I think this is matches the expected logic:

But note for the last 2 rows, I think the original Desired Output contains errors.

WARNING: This is not a pretty workflow, and I hope someone can make it more efficient, but it should be the logic you are looking for.

Time Diff Example.yxmd

Draymora

Thank you for the help! mostly worked - just had to tweak one or two things (it kept truncating the before and after lunch fields for some reason). I was wondering - would I be able to use something similar to determine if multiple event As happened back to back as well? (seeing the time difference for these too, same thing with it being within the same day & for each person)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703