YearFrac with Basis 1

[pgindi002]

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

[jdunkerley79 · Accepted solution]

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

[pgindi002]

This is exactly what I needed! It works perfectly. Thank you so much for your help, and especially for the sample workflow.

[jdunkerley79]

You're welcome - happy to help

[FláviaB]

@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" 😉 

[pgindi002]

Thanks Flavia - I accepted the solution