Hello All,
I am working on a project that involves tracking a credit. The tool uses data validation to trigger certain functions within the formula that I am being challenged with.
This is the formula:
=IFERROR(ROUND(IF(A17="TTD",$G$5*E17,IF(A17="TPD",($G$4/30.416*E17)-(B17))*0.6667)+IF(A17="PERM",$G$7/DAY(DATE(YEAR(C17),MONTH(C17)+1,1)-1)*E17),2),"")
(see file rows 16 and 17)
My challenge is with the part of the formula when "perm" is selected.
When "Perm" is selected this indicates a full monthly benefit (for the most part) but in some cases usually at the being or the end a partial month is needed.
When this happens (partial payment) what is happening now is the formula takes the monthly benefit and divides it into the number of day in the month x the number days and returns the amount of credit.
What I want to happen is for the formula to continue to recognize if a full month is entered (usually from _/1/YEAR- _OEM/YEAR) and return the monthly benefit. In other words whether there be 28 days in the month or 31 the formula will return the same monthly benefit. However, and this is my issue, if there is a partial payment input let’s say 1/1/2014-01/14/2014 , I want the formula to take the monthly benefit /30.416 x by number of days and return the value.
Is this possible? Any suggestions are appreciated
I am working on a project that involves tracking a credit. The tool uses data validation to trigger certain functions within the formula that I am being challenged with.
This is the formula:
=IFERROR(ROUND(IF(A17="TTD",$G$5*E17,IF(A17="TPD",($G$4/30.416*E17)-(B17))*0.6667)+IF(A17="PERM",$G$7/DAY(DATE(YEAR(C17),MONTH(C17)+1,1)-1)*E17),2),"")
(see file rows 16 and 17)
My challenge is with the part of the formula when "perm" is selected.
When "Perm" is selected this indicates a full monthly benefit (for the most part) but in some cases usually at the being or the end a partial month is needed.
When this happens (partial payment) what is happening now is the formula takes the monthly benefit and divides it into the number of day in the month x the number days and returns the amount of credit.
What I want to happen is for the formula to continue to recognize if a full month is entered (usually from _/1/YEAR- _OEM/YEAR) and return the monthly benefit. In other words whether there be 28 days in the month or 31 the formula will return the same monthly benefit. However, and this is my issue, if there is a partial payment input let’s say 1/1/2014-01/14/2014 , I want the formula to take the monthly benefit /30.416 x by number of days and return the value.
Is this possible? Any suggestions are appreciated