Calculate gap in a set of time ranges

[arsh6013]

Problem statement:

I am trying to solve a problem of continuity in time ranges and create a flag(continuous/gap).The goal is to evaluate all time values pairs and make sure there is no gap between time 11(start time) and time 42(end time). These time range sets will possibly go up in real world. So there might even be a Time81 and Time82 pair as well.

My dataset looks like this:

Primary Key	Date	Time11	Time12	Time21	Time22	Time31	Time32	Time41	Time42	Flag
1	11/11/2019	7:00:00	13:00:00	7:00:00	15:00:00	9:00:00	18:00:00	13:00:00	21:00:00	Continous
2	11/12/2019	8:00:00	18:00:00	9:00:00	15:00:00	15:30:00	21:30:00			Continous
3	11/13/2019	8:00:00	17:00:00	9:45:00	15:45:00	16:00:00	21:00:00			Continous
4	11/14/2019	8:00:00	13:30:00	14:00:00	21:00:00					Gap
5	11/15/2019	8:00:00	13:30:00	12:00:00	14:00:00	14:30:00	18:00:00			Gap

I was trying to work with something like this but I am getting to many false positives.

if 
DateTimeDiff([Timings21],[Timings12],"minutes")>0
or
DateTimeDiff([Timings31],[Timings22],"minutes")>0
or
DateTimeDiff([Timings41],[Timings32],"minutes")>0
or
DateTimeDiff([Timings31],[Timings12],"minutes")<0
then
'Gap'
else
'Continuous'
endif

Any help or guidance is highly appreciated. Let me know if there is more clarity needed.

Thanks in advance.

[estherb47]

Hi @arsh6013 

A little more clarity would be helpful. Are you looking to compare all time pairings to see if there is a difference? Are you looking for a pair to pair gap vs. continuous, or gap/continuous for all of the pairs.

A quick look at this dataset and I'm still unclear at what makes something continuous or gap, because there are gaps in all of the different time pairs when you look across. 

thank you & cheers!

Esther

[PhilipMannering · Accepted solution]

If I've understood you correctly (which I almost certainly haven't) you could try this,

Workflow

[arsh6013]

@PhilipMannering That is exactly what I was looking for. The method works great even if I have to increase the time slots to more than 4.

@Esther

The comparison was suppose to happen across all pairs and flag if there is any gap in between the time ranges( The highest and the lowest time are the bounds in which the gap needs to be validated)