YearFrac with Basis 1

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

Expression

Accepted answers

jdunkerley79

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

ActAct.yxmd

All comments

jdunkerley79

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

ActAct.yxmd

pgindi002

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317