Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Matching if a datetime is between a range of datetimes

5 - Atom

Hi, I am a little new to the world of Alteryx & after racking my brain over a problem for quite some time, I seek help. So here goes:


I am trying to find if a particular person placed an order between a set of dates. I have two input files -


  • File 1, with phone numbers & order time of individuals (there can be multiple orders from one person). This is how it looks: (I have 2 million such records)

    Mobile number

    Unique order ID

    Order DateTime



    2019-09-17 23:00:00



    2019-09-18 00:10:00



    2019-09-17 20:10:00

  • File 2, with the set of dates that I need to check the order time in. This is how it looks (1600 records)
    EventStart timeEnd time
    A2019-09-17 22:00:002019-09-18 00:15:00
    B2019-09-17 22:00:002019-09-18 00:15:00
    C2019-09-17 19:55:002019-09-17 22:10:00
    The gap between start & end time stays constant throughout the data i.e.135 mins, however, the date can be different in the columns   


I need to be able to see if order placing time of 'File 1' customers lies between any of the start & end times file 2 taken one row at a time.  So basically each order time from 'File 1" needs to be checked whether or not it falls between the start & end times stated in one row of 'File 2".


I would also require which event it corresponds to but I understand that in case of overlap of event times that might be difficult. If you guys can guide here, that would be of great help as well.  


Required output would be something like this:

Mobile numberOrder DateTimeCondition matchEvent


2019-09-17 23:00:00

YA (& B)


2019-09-18 00:10:00

YA (& B)


2019-09-17 20:10:00



For me, the problem was: 

  1. How to join the two files (I was going to append the file but I believe it would create a very huge dataset to manage)

  2. How to check if order datetime is between the times in 'File 2' checked one row at a time


Any help is much appreciated!

13 - Pulsar

Hi @KanMag 


Here is a macro that will let you do an advanced join based on a condition. In your case, I put in a formula to check that the Order DateTime was within range of the Start Time and End Time. 


[Start time]<[Order DateTime] AND [Order DateTime]<[End time]




After the join, I used a quick Summarize Tool to remove duplicate matches and Concatenate the Events that matched. 




Attached is the packaged workflow with the macro. 


If this solves your issue please mark the answer as correct, if not let me know!





Hi @KanMag - Here is one more idea to consider:




20 - Arcturus
20 - Arcturus

@KanMag ,


I use a GENERATE ROWS tool to create every unit of time (in your case minutes) between the event start and end times.  The formula is a little confusing and so I've created a helpful macro that allows you to point to the event table and it does the expression for you.  That macro is:  CReW Generate Date Rows 


If you take File 1 into a JOIN and beneath that, take File 2 through the macro and connect that macro to the same JOIN, you can join on Order Date Time = Out Date.


You'll configure the macro to increment by minutes and it will be creating every minute for each event.  You will join only on the exact minute that matches (if any) and you'll get a EVENT field that matches.





Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
5 - Atom

@ArtApa Simplest solution, you're a savior!


Thank you everyone, you've been a great help!

6 - Meteoroid

This is an elegant solution, and I was getting an error when using "days" as a parameter for day increments in DateTimeAdd([Start_date],1,"day") but "day" worked.