Prorate Calculation

[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!

 

[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])))

 

[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

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.

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!

 

[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!

 

[Daryl S]

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

 

[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!

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)

 

[Randy]

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

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

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!