Getting time in correct format to be able to see total minutes and difference in minutes

[mcas1]

I have:

01 23:58:23 and 02 00:02:11 where the format is "Day of month hour:minute:second" in UTC. this would be an order placed at 11:58pm on day 1 of the month and it's delivered at 12:02am on the next day. I need to see total time in minutes to deliver, and the difference as well in minutes. I also need to convert the time from UTC to PST.

There will be cases with same day and overnight deliveries

Help on any of the above would be very appreciated!

Thanks!

[MarqueeCrew]

@mcas1 ,

 here's a post to help:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/UTC-to-Central-Time-conversion/td-p/62...  

you can modify the solution by one hour to get you Eastern time. 

cheers,

 mark

[mcas1]

Thanks but my data is in a weird format so it's not that simple as far as i can tell.

[mcas1]

@MarqueeCrew ,

Still unable to figure it out. I have a day/time that goes from May 31 to June 1, but with DateTimeAdd, for some reason rather than going to June 1, it goes to April 30th. Any advice appreciated this is time sensitive. Thanks very much.

My method is:

1. Adding "2013-05" to the date so that it becomes "YYYY-MM-DD HH:MM:SS ...." then i tried using DateTimeAdd.

THanks

[AngelosPachis]

Hi @mcas1 ,

Can you provide a sample of the dataset so we can see how the data looks like? To find the difference between the order and delivery date, why don't you give DateTimeDiff function a try?

[mcas1]

This is the raw data. First needs to be converted from UTC to PST. 

Treat this as Column A to D. I need to be able to see difference between D-A and C-A. The problem i'm having is with dates below where they cross from 31st of one month to the 1st of another. All other dates within the month appear to be working properly.

Thank you for the help i appreciate it.

31 23:59:56	01 18:12:54	01 18:15:03	01 18:42:18
31 23:59:46	01 00:39:04	01 01:08:03	01 01:49:07
31 23:58:42	01 00:30:11	01 01:08:55	01 01:16:24
31 23:58:34	01 00:00:51	01 00:10:59	01 00:46:20
31 23:58:16	01 00:00:54	01 00:10:04	01 00:34:24
31 23:57:58	31 23:59:55		01 00:46:06
31 23:57:16	31 23:58:17	01 00:10:13	01 00:31:26
31 23:57:04	01 00:11:51	01 00:41:49	01 01:03:07
31 23:56:32	31 23:56:55	01 00:08:37	01 00:57:28
31 23:56:14	31 23:56:46	01 00:14:27	01 00:34:19
31 23:55:26	01 17:38:38	01 18:15:56	01 18:55:49
31 23:54:20	31 23:56:10	01 00:08:46	01 00:35:48
31 23:54:16	31 23:55:54	01 00:15:11	01 00:40:40
31 23:54:10	31 23:55:48	01 00:02:40	01 00:19:21
31 23:52:48	31 23:53:34		01 00:59:50
31 23:49:47	31 23:51:14	01 00:19:46	01 00:46:05
31 23:46:25	31 23:47:43	31 23:56:34	01 00:22:23
31 23:46:02	01 00:09:26

[AngelosPachis · Accepted solution]

Hi @mcas1 ,

I have created a workflow that probably answers your question. It follows a brute force method, so it is not very flexible if for example you had more than 4 columns, then the workflow wouldn't work.

The logic I've used was to first convert column A to a date. Then every event because it follows the event located in the previous column, should look at that previous column to get information about the year and the month. If the day number of a column is less than that of a previous column, then that means that the months has changed and so the month for this column would be the previous month +1. Same logic for a jump in year.

To convert from UTC to PST I used a multi-field formula tool and subtracted 8 hours from all fields.

Hope it works for you, let me know if we should come up with a more robust solution or this one would suffice and is good enough.

Regards,

Angelos

[mcas1]

Wow - thank you so much. That did it. You are a pro, and those formulas are impressive. Thank you so much for taking the time to help me.