calculate months with days

[theDBguy]

Hi. If Bookkeeping Department says a month=30 days, then I think you could just try this:

DateDiff("d",[StartDate],[EndDate])/30

 

[isladogs]

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:

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

 

[Gasman]

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?

 

[isladogs]

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'

 

[eshai]

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:

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

 

[isladogs]

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

 

[Dreamweaver]

If you download my Employees Example you will see there is an age field on the employee form you could copy the function just give it a date and it will do what you need plus it's simple to alter.


https://databasedreams.createaforum.com/employees-example/employees-example-version-4-download/


Mick

 

[Cronk]

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

 

[eshai]

Thank you all

I solved it
that what i did

gives me the amount of months

months: Diff2Dates("m",[StartDate],[EndDate])

gives me the amount of months and days

[CODE]months_days: Diff2Dates("md",[StartDate],[EndDate])

[/CODE]

this code well extract the number from the string

days: Mid([months],11,2)

this will give the payment where A [monthlypayment] is equal to what I will determine

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