DateTimeDiff, rounding help
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Custom Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
 
You can also change your output type to a FixedDecimal of 10.2 and it will round it to the 2 decimal places.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I don't why I thought I had to do all that, but @chukleswk approach is much simpler, here is the example.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
