community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Formula for returning the decimal value that represents fractional months between two date

Highlighted

I'm currently trying to calculate fractional months within alteryx; however am running into some errors. I've only been able to come across datedtimediff - however, it returns the difference in months as a whole number. I'm looking for a similar function to the excel function YEARFRAC.

 

 

Bolide
Hi

You can definite the duration that’s measured in the final part of the function: years, months, days, hours, minutes, or seconds

Could you use “days” and divide by 365? I’m not familiar with YEARFRAC in excel but would that give you the same thing?

Day count conventions are always a bit of a pain. I think:

 

 

((DateTimeYear([End])*12 + DateTimeMonth([End]) - DateTimeYear([Start])*12 - DateTimeMonth([Start]))*30 + 
DateTimeDay([End]) -
IIF(LEFT(DateTimeAdd([Start],1,"days"),8)!=Left([Start],8),30,DateTimeDay([Start])))/360

 

 

This should work out the 30/360 default (YearFrac standard basis) reasonably accurately (no promises I haven't missed an edge case)

 

This worked like a charm, thank you!! I just had to multiply the equation by 12 to return months opposed to years for purposes of the workflow I'm building. Thanks again.

Labels