Prorate Calculation

R

Randy

Greetings...

I am attempting to create a prorate calculator of sorts and in need of some
assistance with my formula/code....

I need the calculator to calculate the amount based on the number of days in
a month. For instance, if the month has 31 days I need the calculation to
calculate based on 31 days, if the month has 30 days the calculation should
calculate based on 30 days.

Here is what I have so far:

=IIf(Day([Move_In_Date])=1,"n/a",IIf(Month([Move_In_Date])=1 Or
IIf(Month([Move_In_Date])=3,([Rent_Amount]/31)*Sum(31-(DatePart("d",[Move_In_Date]))+1),([Rent_Amount]/30)*Sum(30-(DatePart("d",[Move_In_Date]))+1)))

I keep receiving a "You hve too many arguments" error....can any assist or
see what my problm might be.

Thanks in advance for your assistance!
 
J

Jerry Whittle

I think that you need to step back and rethink this. First of all there are
many more months than just January and March that have 31 days.

Then there's the little problem with February which can have 28 or 29 days.

See if something like below gets you close without all the IIf grief.

[Rent_Amount]*Day([Move_In_Date])/Day(DateAdd("m",1,[Move_In_Date])-Day([Move_In_Date])))
 
R

Randy

Thanks Jerry...I will play with this a little...and yes I am aware of the
additional months....I was just looking for a start then I could tweak
accordingly...However I do like your method and will give it a shot...
--
Randy Street
Rancho Cucamonga, CA


Jerry Whittle said:
I think that you need to step back and rethink this. First of all there are
many more months than just January and March that have 31 days.

Then there's the little problem with February which can have 28 or 29 days.

See if something like below gets you close without all the IIf grief.

[Rent_Amount]*Day([Move_In_Date])/Day(DateAdd("m",1,[Move_In_Date])-Day([Move_In_Date])))

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Randy said:
Greetings...

I am attempting to create a prorate calculator of sorts and in need of some
assistance with my formula/code....

I need the calculator to calculate the amount based on the number of days in
a month. For instance, if the month has 31 days I need the calculation to
calculate based on 31 days, if the month has 30 days the calculation should
calculate based on 30 days.

Here is what I have so far:

=IIf(Day([Move_In_Date])=1,"n/a",IIf(Month([Move_In_Date])=1 Or
IIf(Month([Move_In_Date])=3,([Rent_Amount]/31)*Sum(31-(DatePart("d",[Move_In_Date]))+1),([Rent_Amount]/30)*Sum(30-(DatePart("d",[Move_In_Date]))+1)))

I keep receiving a "You hve too many arguments" error....can any assist or
see what my problm might be.

Thanks in advance for your assistance!
Click to expand...
Click to expand...
 
D

Daryl S

Randy -

This will calculate the first month's rent, pro-rated as needed, and is
valid for all months, and even leap years:

=DateDiff("d",[Contract Date],DateSerial(Year(DateAdd("m",1,[Contract
Date])),Month(DateAdd("m",1,[Contract
Date])),1))/DatePart("d",DateAdd("d",-1,DateSerial(Year(DateAdd("m",1,[Contract Date])),Month(DateAdd("m",1,[Contract Date])),1)))*[Rent_Amount]

It looks nasty, but it basically calculates the number of days to the first
of the next month and divides it by the number of days in the current month,
and finally multiplies it by the Rent Amount.

Check it out - hope that helps!
 
D

Daryl S

In my last posting, change [Contract Date] to [Move_In_Date] everywhere it
appears. Sorry about that!
 
J

James A. Fortune

Greetings...

I am attempting to create a prorate calculator of sorts and in need of some
assistance with my formula/code....

I need the calculator to calculate the amount based on the number of days in
a month. For instance, if the month has 31 days I need the calculation to
calculate based on 31 days, if the month has 30 days the calculation should
calculate based on 30 days.

Here is what I have so far:

=IIf(Day([Move_In_Date])=1,"n/a",IIf(Month([Move_In_Date])=1 Or
IIf(Month([Move_In_Date])=3,([Rent_Amount]/31)*Sum(31-(DatePart("d",[Move_In_Date]))+1),([Rent_Amount]/30)*Sum(30-(DatePart("d",[Move_In_Date]))+1)))

I keep receiving a "You hve too many arguments" error....can any assist or
see what my problm might be.

Thanks in advance for your assistance!
Click to expand...

See:

http://groups.google.com/group/comp.databases.ms-access/msg/e0e9cb55a8a8729a

Also note that the DateSerial() function can be used to obtain the
first and last date of the month.

Example:

tblDates
ID AutoNumber
StartDate Date/Time
EndDate Date/Time
ID StartDate EndDate
1 2/28/07 2/28/07
2 1/29/07 2/28/07
3 3/5/07 4/18/07
4 4/1/07 5/1/07

qryDaysOverlap:
SELECT StartDate, EndDate, DateSerial(Year(StartDate), Month
(StartDate), 1) As MonthStart, DateSerial(Year(StartDate), Month
(StartDate) + 1, 0) As MonthEnd, DateIntersection(MonthStart,
MonthEnd, StartDate, EndDate) As DaysOverlap FROM tblDates;

!qryDaysOverlap:
StartDate EndDate MonthStart MonthEnd DaysOverlap
2/28/07 2/28/07 2/1/07 2/28/07 1
1/29/07 2/28/07 1/1/07 1/31/07 3
3/5/07 4/18/07 3/1/07 3/31/07 27
4/1/07 5/1/07 4/1/07 4/30/07 30

I chose the month containing the StartDate as one way to ensure that
the date ranges would always contain some overlap.

You might want something like:

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
4 4
5 5
....

SELECT #3/1/07# As ReferenceDate, DateAdd("m", tblI.I - 1,
ReferenceDate) As theMonth, StartDate, EndDate, DateSerial(Year
(theMonth), Month(theMonth), 1) As MonthStart, DateSerial(Year
(theMonth), Month(theMonth) + 1, 0) As MonthEnd, DateIntersection
(MonthStart, MonthEnd, StartDate, EndDate) / Day(MonthEnd) AS
FractionOfMonth FROM tblDates, tblI WHERE I <= 12;

to give you a year of month ranges for each StartDate and EndDate in
tblDates.

Once you get the hang of it, you can replace the DateIntersection()
function with equivalent SQL. Have fun.

James A. Fortune
(e-mail address removed)
 
R

Randy

You my friend are AWESOME! Worked like a charm! Thank you Thank you Thank you!
--
Randy Street
Rancho Cucamonga, CA


Daryl S said:
Randy -

This will calculate the first month's rent, pro-rated as needed, and is
valid for all months, and even leap years:

=DateDiff("d",[Contract Date],DateSerial(Year(DateAdd("m",1,[Contract
Date])),Month(DateAdd("m",1,[Contract
Date])),1))/DatePart("d",DateAdd("d",-1,DateSerial(Year(DateAdd("m",1,[Contract Date])),Month(DateAdd("m",1,[Contract Date])),1)))*[Rent_Amount]

It looks nasty, but it basically calculates the number of days to the first
of the next month and divides it by the number of days in the current month,
and finally multiplies it by the Rent Amount.

Check it out - hope that helps!

--
Daryl S


Randy said:
Greetings...

I am attempting to create a prorate calculator of sorts and in need of some
assistance with my formula/code....

I need the calculator to calculate the amount based on the number of days in
a month. For instance, if the month has 31 days I need the calculation to
calculate based on 31 days, if the month has 30 days the calculation should
calculate based on 30 days.

Here is what I have so far:

=IIf(Day([Move_In_Date])=1,"n/a",IIf(Month([Move_In_Date])=1 Or
IIf(Month([Move_In_Date])=3,([Rent_Amount]/31)*Sum(31-(DatePart("d",[Move_In_Date]))+1),([Rent_Amount]/30)*Sum(30-(DatePart("d",[Move_In_Date]))+1)))

I keep receiving a "You hve too many arguments" error....can any assist or
see what my problm might be.

Thanks in advance for your assistance!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Month days over a time period 5
Categorize Continuous Variable into a Discrete One based on a table 1
IIF in Crosstab Expression 0
fill range with dates based on month 0
Date Calculation - Astro Calculation 0
Search yields no results to calculate number of days based on Rang 5
Days of the month 1
Formulas 2

Top