Recreate formula in Alteryx from Excel - number of months amortizing in current year

[Gmonaco]

Hi Alteryx Community,

I'm attempting to replicate a complex formula from Excel into Alteryx and I'm completely spinning my wheels trying to replicate this. The formula is to calculate the number of months a project has been amortizing in the current year using a project in-service date, the useful life of the project, the start of the year, and the remaining useful life (in months). 

I've attached the excel file with sample data and highlighted column E "Months Amortizing in Current Year" which contains the formula I'm trying to replicate and also attached an Alteryx workflow which contains the same excel file with a formula tool attached to the input data tool.

Any help replicating this formula is EXTREMELY appreciated!

Thank you!

[Raj]

This will work

IIF([In-Service Date]<[Start of Year:],IIF([Useful Life (months)]-
DateTimeDiff([In-Service Date],[Start of Year:],"months")<0,0,([Useful Life (months)]-DateTimeDiff([In-Service Date],[Start of Year:],"months"))),[Useful Life (months)])-[Remaining Useful Life]

[DavidSkaife · Accepted solution]

Hi @Gmonaco 

Try this formula, the output matches what you have in the example file:

(IF [In-Service Date] < [Start of Year:]
AND ([Useful Life (months)] - DateTimeDiff([Start of Year:],[In-Service Date],'Months'))<0
THEN 0
ELSEIF [In-Service Date] < [Start of Year:]
THEN ([Useful Life (months)] - DateTimeDiff([Start of Year:],[In-Service Date],'Months'))
ELSE [Useful Life (months)]
ENDIF) - [Remaining Useful Life]

[Gmonaco]

This is incredible! Thank you so much! 

Appreciate your help.