New User Needs Help

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I am attempting to create a formula where I can figure
out the pro-rated amount owed for billing statements.

Basically, I want to be able to input the start date, end
date, and monthly amount and figure out the amount over
the period of time. For months which include the entire
month I need the whole monthly amount to be calculated.
For dates at the end of a month, or start of a month,
where the entire month is not being used, I want the
monthly amount divided by the total number of days in the
month, and multiplied by the days actually used that
month, and have all of this totaled up to give the total
amount owed.

Could you please help me with this problem I am having
difficulties with?

Thanks in advance,

Jason
 
Hi

With StartDate in A2, EndDate in B2 and MonthlyAmount in C2, and StartDate
and EndDate included into time period
=C2*(MONTH(B2)-MONTH(A2)+1-(A2-DATE(YEAR(A2),MONTH(A2),1))/(DATE(YEAR(A2),MO
NTH(A2)+1,1)-DATE(YEAR(A2),MONTH(A2),1))-(DATE(YEAR(B2),MONTH(B2)+1,0)-B2)/(
DATE(YEAR(B2),MONTH(B2)+1,1)-DATE(YEAR(B2),MONTH(B2),1)))


Arvi Laanemets
 
Back
Top