Calculating numbers using dates

[Peanuts]

I have a report page that has to calculate a formula using the days in a month. The formula I use on my job is say: $500.00 divided by 30 days, = $16.66 per day Times a number of days left in any given month. So, if the purchase date was the 8th day of March, then it would be 24 days times the $16.66 equaling $399.99 or round up to $400.00.

All through out my tables, forms and reports I have used the date format as 03/03/2003. I have my report calculating every thing correctly except when I/it gets to multiplying the daily rate times the number of days.

How do I get the the calculation to multiply the remaining days, or to pick just the day out of the middle of the formated date? I have tried to convert the date to a computer number but I get no answer or some number way out there and no where close to what the correct answer is.

Any Ideas? Thanks for your help and input.

 

[raskew]

The following will hopefully provide enough examples so you can sort out the problem.

To use, copy/paste the code to a new module then, from the debug window, type ? CalcPayments <enter>

Function CalcPayments()
Dim dteStart, curAmt, intNumDays, curDaily, intDaysLeft, curForMonth
Dim Fmt, msg, msg1, NL, TB, xyz

Fmt = "###,###,##0.00"  ' Define money format.
NL = Chr(13) & Chr(10)  ' Define newline.
TB = Chr(9) ' Define tab.

msg1 = "Enter the start date (mm/dd/yyyy)."
dteStart = DateValue(InputBox(msg1))
'number of days in month
intNumDays = day(DateSerial(Year(dteStart), Month(dteStart) + 1, 0))
intDaysLeft = intNumDays - day(dteStart)

'Message box header
msg = "Payment Calculation" & NL & NL

msg = msg & "StartDate: " & dteStart & NL
msg = msg & "Number of days in month: " & intNumDays & NL
msg = msg & "Number of days left in month: " & intDaysLeft & NL

msg1 = "Enter the purchase amount."

curAmt = Format(InputBox(msg1), Fmt)

msg = msg & "Purchase amount: $" & curAmt & NL

curDaily = Format(curAmt / intNumDays, Fmt)
msg = msg & "Amount per day: $" & curDaily & NL

curForMonth = Format(curDaily * intDaysLeft, Fmt)
msg = msg & "Payments for month: $" & curForMonth & NL & NL
msg = msg & "Bal. remaining at end of month: $" & Format(curAmt - curForMonth, Fmt)
xyz = MsgBox(msg, vbInformation, "In response to your enquiry")
msg = "Number of days in a month (last day of month): " & NL
msg = msg & "intNumDays = day(DateSerial(Year(dteStart), Month(dteStart) + 1, 0))" & NL & NL
msg = msg & "Balance (curAmt) spread over number of days in month" & NL
msg = msg & "curDaily = curAmt / intNumDays" & NL & NL
msg = msg & "Payments spread over remaining days in month (curForMonth)" & NL
msg = msg & "curForMonth = curDaily * intDaysLeft" & NL
xyz = MsgBox(msg, vbInformation, "Calculations Used")
End Function