In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Finding midpoint of datetime between two datetime columns

Sabih
6 - Meteoroid

I have searched high and low on the Alteryx forum and not been able to fine the answer to my current task.

 

I have two columns, the first with a start date/time (OCC DATE) and the second with the end date/time (FND DATE). Many of the records have just seconds apart but many of them have minutes, hours, and even days apart. I need to find the midpoint! I need the results in the time format of  hh:mm form ( the following data I have is in 24 hr). (since some of the points are just one second apart, I don't know how that will affect it but I just need it up the minute.. I don't care about the seconds). 

 

Once I get the midpoint, will extract the time out of the midpoint date/time (which I know how to do). Then create a function saying if the midpoint is between 07:00 - 15:00 then shift 2, if between 15:01 - 23:00 then shift 3, and 23:01 - 06:59 then shift 1, However in order to do that, I need to first get the midpoint. I know I need to include day because if we are talking about time over a 24 hour period then the midpoint would be screwed up if I only used time. 

 

OCC DATE                          FND DATE
2017-01-01 02:37:51      2017-01-01 02:37:52
2017-01-01 03:53:00      2017-01-01 03:53:00
2017-01-01 07:53:08      2017-01-01 07:53:09
2017-01-01 08:11:34      2017-01-01 08:11:35
2017-01-01 06:18:32      2017-01-01 06:18:33
2016-12-29 07:45:00      2016-12-29 17:00:00
2016-12-29 07:45:00      2016-12-29 17:00:00
2017-01-01 10:42:33      2017-01-01 10:42:34
2017-01-01 13:12:15      2017-01-01 13:12:16
2017-01-01 11:23:36      2017-01-01 11:23:37
2016-12-31 17:00:00      2017-01-01 13:00:00
2017-01-01 15:05:00      2017-01-01 15:07:00
2017-01-01 17:45:44      2017-01-01 17:45:45
2017-01-01 15:02:00      2017-01-01 16:01:13
2017-01-01 14:45:00      2017-01-01 15:02:00
2017-01-01 00:00:00      2017-01-01 00:00:00
2017-01-02 02:51:51      2017-01-02 02:51:52
2016-12-30 19:26:00      2016-12-30 19:26:00
2017-01-01 02:30:00      2017-01-01 13:08:00
2017-01-01 18:45:10      2017-01-01 19:11:11
2017-01-01 19:11:45      2017-01-01 19:11:46
2017-01-01 18:00:00      2017-01-01 18:00:00
2017-01-01 20:05:54      2017-01-01 20:05:55
2017-01-01 23:29:44      2017-01-01 23:29:45
2017-01-02 02:20:59      2017-01-02 02:21:00
2017-01-01 17:00:00      2017-01-02 10:28:03

 

Any help will be much appreciated!

 

 

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus
Datetimediff([fnd date],[occ date],"seconds")

Why not divide that by 2 and add the result

Datetimeadd([fnd date], (Datetimediff([fnd date],[occ date],"seconds")/2),"seconds)

That might work?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Sabih
6 - Meteoroid

Thank you for responding!

 

So , I tried what you said out and I don't think it is quite right. I think it may just be adding the time difference or something to the fnd date. Here is what I got.. 

OCC DATE                                        FND DATE                      result
2016-12-29 07:45:00 2016-12-29    17:00:00 2016-12-29      21:37:30
2016-12-29 07:45:00 2016-12-29    17:00:00 2016-12-29      21:37:30
2017-01-01 10:42:33 2017-01-01    10:42:34 2017-01-01      10:42:34
2017-01-01 13:12:15 2017-01-01    13:12:16 2017-01-01     13:12:16
2017-01-01 11:23:36 2017-01-01    11:23:37 2017-01-01      11:23:37
2016-12-31 17:00:00 2017-01-01    13:00:00 2017-01-01     23:00:00
2017-01-01 15:05:00 2017-01-01   15:07:00 2017-01-01      15:08:00
2017-01-01 17:45:44 2017-01-01   17:45:45 2017-01-01      17:45:45
2017-01-01 15:02:00 2017-01-01   16:01:13 2017-01-01      16:30:49  Instead of being midpoint between 15:02 and 16:01, it just added on to fnd date
2017-01-01 14:45:00 2017-01-01    15:02:00 2017-01-01    15:10:30  Here too. Midpoint should something like, 14:53:50
2017-01-01 00:00:00 2017-01-01    00:00:00 2017-01-01     00:00:00
2017-01-02 02:51:51 2017-01-02    02:51:52 2017-01-02     02:51:52

Sabih
6 - Meteoroid

Ahh! instead of addtime to to FND DATE, I just need to add to OCC Date. Thus, 

 

DateTimeAdd([OCC DATE], (DateTimeDiff([FND DATE],[OCC DATE],"seconds")/2),"seconds")

 

Thank you very much for your help!!! 

 

Labels
Top Solution Authors