Finding midpoint of datetime between two datetime columns

[Sabih]

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!

[MarqueeCrew · Accepted solution]

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?

[Sabih]

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]

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!!!