Hi,
My data is in below format. I wanted to subtract two dates like this 3/22/2017 14:20 -3/22/2017 14:21 for each number. Date format is in below.can any one help on this? Please let me know if you have any questions.
Number | UpdatedDate |
AAA | 3/22/2017 14:20 |
AAA | |
AAA | 3/22/2017 14:21 |
BBB | 9/29/2017 16:02 |
BBB | |
BBB | |
BBB | 9/29/2017 16:29 |
CCC | 2/22/2018 15:48 |
CCC | |
CCC | 2/22/2018 16:19 |
Solved! Go to Solution.
Hello!
Give the following a try... first, use the DateTime tool to convert your dates to the standard Alteryx format yyyy-mm-dd hh:mm:ss... and then use a Summarize tool to find the Min & Max DateTime per Number. You can then use a formula (DateTimeDiff) to find the difference between the min/max dates to determine the time between records.
See attached! (V11.8)
Cheers,
NJ
This is relatively straight forward.
Firstly we need to use the datetime tool to bring your datetime field into the appropriate format.
Use the filter tool to remove the blank records.
You can then summerize your data and return the MIN updateddate and MAXUpdated date, and then use the datetimediff() function in the formula tool to create your time difference.
See the attached workflow.
Ben
Thanks for your response!
The formula is working except few dates. Date difference is not calculating few of the dates. I have pasted few of the date samples which are not calculated pasted below. Can you help on this?
Forex:
Max_DateTime_out | Min_DateTime_Out |
02/09/2018 8:03:41 | 02/08/2018 15:32:42 |
02/05/2018 14:13:29 | 03/01/2018 12:03:50 |
02/06/2018 10:20:01 | 02/08/2018 11:18:46 |
Thanks for your response!
The formula is working except few dates. Date difference is not calculating few of the dates. I have pasted few of the date samples which are not calculated pasted below. Can you help on this?
Forex:
Max_DateTime_out | Min_DateTime_Out |
02/09/2018 8:03:41 | 02/08/2018 15:32:42 |
02/05/2018 14:13:29 | 03/01/2018 12:03:50 |
02/06/2018 10:20:01 | 02/08/2018 11:18:46 |
I probably should read your question before I post a solution, but here is some code:
DateTimeDiff(DateTimeParse([Max_DateTime_out],"%m/%d/%Y %H:%M:%S"),DateTimeParse([Min_DateTime_out],"%m/%d/%Y %H:%M:%S"),"minutes")
This will convert the formatted date string to a date/time field and will compare the number of minutes between the two field values. You could tweak it to your liking.
Cheers,
Mark
I'm not too sure who's solution you are using, but I just tested with mine and it seems to work fine.
Can you confirm it is my method you are using and what particularly you mean by 'not working'?
Ben
I am receiving conversion errors by using the below formula.
DateTimeDiff(DateTimeParse([Max_DateTime_out],"%m/%d/%Y %H:%M:%S"),DateTimeParse([Min_DateTime_out],"%m/%d/%Y %H:%M:%S"),"minutes")
It worked with the sample data that was provided :( on my computer. Maybe you have multiple formats of data in the fields?