DateTimeDiff, rounding help

[wonka1234]

Here is what I get in excel:

excel formula - =(V13-V11)/365.25

what im trying in alteryx:

DateTimeDiff([Maturity Date],[Run Date],"month")/12

but i am getting 7.0 . 

Is there a way to round this to get 6.50 like in excel?

[gabrielvilella]

Hi @wonka1234, try this one:

DateTimeDiff([Maturity Date],[Run Date],'years')+(DateTimeDiff([Maturity Date],[Run Date],'months')-DateTimeDiff([Maturity Date],[Run Date],'years')*12)/12

[chuckleswk]

 

You can also change your output type to a FixedDecimal of 10.2 and it will round it to the 2 decimal places.

[wonka1234]

@chuckleswk 

still getting 7.0!

even with the formula

@gabrielvilella 

here are my inputs:

[gabrielvilella]

I don't why I thought I had to do all that, but @chuckleswk approach is much simpler, here is the example.

[chuckleswk]

Do you have a sample file / flow that you can share? Are you changing 30-Sep-19 and 31-Mar-26 into a date that alteryx can handle (using the DateTime Parser or a formula)?

[wonka1234]

@gabrielvilella @chuckleswk 

I convert the date from a string like this:

it comes in as a string because I use  dynamic rename.. I have to use it though otherwise it would be read in from the input as a date time .

[chuckleswk]

that's because you are starting on 2019-01-30 and ending on 2026-01-31...that's 7 years.

Your start date needs to be 2019-09-30

[chuckleswk]

Make sure you are converting the date using the following and please see the attached workflow.

 

[gabrielvilella]

Another tip here is that you don't need multiple Date Time tools one after the other. I prefer using the Multi-field formula to do that in one single tool.