Need Help on Formula

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

Common Use Cases

Accepted answers

NicoleJohnson

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,

DateTime Example.yxmd

BenMoss

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

Workflow.yxmd

MarqueeCrew

@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

Pinky

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

All comments

NicoleJohnson

Hello!

See attached! (V11.8)

Cheers,

DateTime Example.yxmd

BenMoss

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

Workflow.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703