lundi 17 décembre 2018

Nested IF calculating the number of months to return an annual payment amount

I am struggling with a nest IF statement that I can't get to work. I need to calculate the amount being paid each fiscal year based on the start and enddate of a contract, the start and end date of the fiscal year, the annual rate and the monthly rate. Here is my formula:

=IF([@[Contract Start Date]]<=43191,IF([@[Contract End Date]]>=43555,[@[Annual Rate]], 
 IF([@[Contract Start Date]]<=43191,IF([@[Contract End Date]]<43555,(DATEDIF(43190,[@[Contract End Date]],"m")*[@[Monthly Rate]]), 
 IF([@[Contract Start Date]]>43191,IF([@[Contract End Date]]<43555,(DATEDIF([@[Contract Start Date]],[@[Contract End Date]],"m")*[@[Monthly Rate]]), 
 IF([@[Contract Start Date]]>43191,IF([@[Contract End Date]]>=43555,(DATEDIF([@[Contract Start Date]],[@[Contract End Date]],"m")*[@[Monthly Rate]])))))))))

When I try to run the above it returns only the number symbol repeating (no amount of resizing the columns shows a number). What am I doing wrong?

Aucun commentaire:

Enregistrer un commentaire