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

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.

Workflow

Best Practices

Help

Accepted answers

jdunkerley79

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)

DayCount.yxmd

All comments

andyuttley

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

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)

DayCount.yxmd

alexisorozco

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235