Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

alexisorozco
5 - Atom

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.

 

 

3 REPLIES 3
andyuttley
11 - Bolide
11 - 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?
jdunkerley79
ACE Emeritus
ACE Emeritus

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)

 

alexisorozco
5 - Atom

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