Hi all,
Is anyone able to help with how a YearFrac calculation in Excel can be transferred to Alteryx? I've seen posts that provide a YearFrac workflow, but those represent a Basis of 0 (30/360). I am looking to represent a Basis of 1 (actual/actual).
Any guidance is appreciated!
Thanks,
Paige
Gelöst! Gehe zu Lösung.
Interesting challenge.
First make sure Date1 > Date2 and flip if not
Then work out days in each year, something like:
DateTimeDiff(Left([Date1],4)+"-12-31",
Left([Date1],4)+"-01-01",
"days")+1
The expression:
IF DateTimeYear([Date1]) == DateTimeYear([Date2]) THEN
[DaysInYear1]
ELSEIF DateTimeYear([Date2]) - 1 = DateTimeYear([Date1])
AND RIGHT([Date1],5) >= RIGHT([Date2],5) THEN
IF [DaysInYear1] = 366 Then
IIF(RIGHT([Date1],5) < '03-01',366,365)
ELSEIf [DaysInYear2] = 366 Then
IIF(RIGHT([Date2],5) > '02-29',366,365)
ELSE
365
ENDIF
ELSE
(DateTimeDiff(Left([Date2],4)+"-12-31",
Left([Date1],4)+"-01-01",
"days") + 1) / (1 + DateTimeYear([Date2]) - DateTimeYear([Date1]))
ENDIF
will work out the basis for the calculation and then:
DateTimeDiff([Date2],[Date1],"days")/[AnnualBasis]
This will give the correct value for Actual/Actual
Sample workflow attached
This is exactly what I needed! It works perfectly. Thank you so much for your help, and especially for the sample workflow.
You're welcome - happy to help
@pgindi002 I am moving your post to our Discussion Board so other users can find it in case they run into the same problem. In fact, don't forget to "accept as solution" 😉
Thanks Flavia - I accepted the solution