calculate months with days

Hi. If Bookkeeping Department says a month=30 days, then I think you could just try this:
Code:
DateDiff("d",[StartDate],[EndDate])/30
 
Eschai
It would have helped a lot if you had said you were constrained by 'bookkeeping rules of 30 day months' from the start
That means the functions to calculate exact years, months & days are irrelevant

Instead, if I understand you correctly, you need to calculate the number of days between the 2 dates, divide that by 30 to get your bookkeeping months then multiply by your monthly amount

For example:
attachment.php


Code:
SELECT tblPayments.ID, tblPayments.StartDate, tblPayments.EndDate, 
Diff2Dates("ymd",[StartDate],[EndDate]) AS DateDiff, DateDiff("d",[StartDate],[EndDate]) AS Days, 
DateDiff("d",[StartDate],[EndDate])/30 AS 30DayMonths, tblPayments.MonthlyPayment, 
[MonthlyPayment]*(DateDiff("d",[StartDate],[EndDate])/30) AS AmountDue
FROM tblPayments;

NOTE: the DateDiff column is just there for info. It isn't needed in the calculation

Hope that's finally what you need for your bookkeeping...

EDIT: It seems DBG came to the same conclusion whilst I was updating my example
 

Attachments

With the o/p rules though, row 7 would be 1441, row 8 would be 394 and row 9 would be 80, and so on?
 
I only left the DateDiff column in for reference. It isn't used in this calculation.
However the point is certainly valid in that years don't have 12*30 =360 days and that's true even using Eschai's 'book-keeping years'
 
Eschai
It would have helped a lot if you had said you were constrained by 'bookkeeping rules of 30 day months' from the start
That means the functions to calculate exact years, months & days are irrelevant

Instead, if I understand you correctly, you need to calculate the number of days between the 2 dates, divide that by 30 to get your bookkeeping months then multiply by your monthly amount

For example:
attachment.php

isladogs I am grateful for your help

one problem let's take the first record for example
2 years (24 months)multiple by 1200 = "28,800"
2 months multiple by 1200 ="2400"
13 days have to by in this formula 1200 divided by 30 = "40" multiply by 13= "520"
now 28,800+2400+520="31,720‬"

I'm very sorry for all the confusion, I probably have a problem with the wording:o
 
You appear to be confusing your own logic

You can either use the actual number of years, months & days (as the earlier part of this thread was doing) or use the 30 day book-keeping months.
BUT you can't compare them and expect to get the same answer.

You've been given enough information to be able to work out for yourself the correct expression to fit your peculiar business rules. I'm about to sign off for the night and will also bow out of this thread as I don't think there's anything else I can add.

Good luck finding a solution
 
eshai wrote



one problem let's take the first record for example
2 years (24 months)multiple by 1200 = "28,800"
2 months multiple by 1200 ="2400"
13 days have to by in this formula 1200 divided by 30 = "40" multiply by 13= "520"
now 28,800+2400+520="31,720‬"


Seems to me that you are complicating a simple calculation
Number of days = EndDate - StartDate

Number of charge periods = number of days / 30
AmountDue= number of charge periods * PeriodCharge


For first record
AmountDue = (28/7/2018 - 15/5/2016) /30 * 1200 = 32,160


or
AmountDue= (EndDate - StartDate) / 30 * PeriodCharge
 
Thank you all

I solved it
that what i did

gives me the amount of months
Code:
months: Diff2Dates("m",[StartDate],[EndDate])

gives me the amount of months and days
Code:
[CODE]months_days: Diff2Dates("md",[StartDate],[EndDate])
[/CODE]

this code well extract the number from the string
Code:
days: Mid([months],11,2)

this will give the payment where A [monthlypayment] is equal to what I will determine
Code:
payment: [months]*[MonthlyPayment]+[MonthlyPayment]/30*[days]

If you look at the first record it is equal to 32,100

In my calculation the result is equal to 31,720 Which is more accurate for bookkeeping
 
Last edited:

Users who are viewing this thread

Back
Top Bottom