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

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!

Date Time

Data Investigation

Time Series

Accepted answers

AngelosPachis

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

example.yxmd

All comments

MarqueeCrew

@mcas1 ,

here's a post to help:

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

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

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.

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

example.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703