Need Help on Formula

[Pinky]

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

[NicoleJohnson · Accepted 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

[BenMoss · Accepted solution]

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

[Pinky]

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

[Pinky]

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

[MarqueeCrew · Accepted solution]

@Pinky,

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

[BenMoss]

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

[Pinky]

Thank you so much for your quick response!

I have used the same formula but still not working. I see some conversion errors. Formula is not working, Is it because of conversion errors. Anyway i am going to attach the screenshot. Can you help on this?

[Pinky · Accepted solution]

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")

[MarqueeCrew]

It worked with the sample data that was provided :( on my computer.  Maybe you have multiple formats of data in the fields?