calculate months with days (1 Viewer)

[eshai]

hi:
i need to calculate months with days that past from the start of the months
for now i'm using

=DateDiff("m", [start date]'[end date])

now i have a problem let's say the "start date" was 20/10/2019 and the "end date" is 1/12/2019 so its one month and 11 days. Access referred the "20/10/2019" as a full month so i get the result of two months
exemple: a person pays for a month 1200$ and for one day is payings 45$
so for two months he will pay 2400$ but for one month and 11 days he have to pay "1695$"

 

[theDBguy]

Hi. You'll need a custom function to do that. Let me see if I can find out for you if there's any function already created by someone else to do that.

 

[theDBguy]

Hi. You'll need a custom function to do that. Let me see if I can find out for you if there's any function already created by someone else to do that.

Okay, I found two potential candidates for your consideration:


1. GetAge() - You may have to modify this one to use a second date input instead of the default of today's date (for age calculation).


2. Diff2Dates() - You should be able to just use this one.


Hope that helps...

 

[eshai]

thanks i will take a look on that

 

[theDBguy]

thanks i will take a look on that

You're welcome. Let us know how it goes...

 

[eshai]

You're welcome. Let us know how it goes...

I couldn't figure out which field would show the result
And where the function is put. In the form, in the field, in the module etc'

 

[theDBguy]

I couldn't figure out which field would show the result
And where the function is put. In the form, in the field, in the module etc'

So, let's say you decided to use Doug's Diff2Dates() function. You would copy the function from the website into a Standard Module in your Access application and give it a name like modDiff2Dates. Then you can use it in a query. For example:

SELECT [StartDate], [EndDate], Diff2Dates("ymd",[StartDate],[EndDate]) As Diff
FROM TableName

Hope that helps...

 

[eshai]

SELECT [StartDate], [EndDate], Diff2Dates("ymd",[StartDate],[EndDate]) As Diff
FROM TableName

Hope that helps...[/QUOTE]


i get the error "The Syntax of the Subquery in This Expression is Incorrect"
There should be more brackets

 

[theDBguy]

SELECT [StartDate], [EndDate], Diff2Dates("ymd",[StartDate],[EndDate]) As Diff
FROM TableName

Hope that helps...

i get the error "The Syntax of the Subquery in This Expression is Incorrect"
There should be more brackets[/QUOTE]

Hi. You'll have to show us what you tried, so we can tell you what's wrong with it.

 

[eshai]

Hi. You'll have to show us what you tried, so we can tell you what's wrong with it.

i made a new db for check
created a table "table1" with the fields "date1", "date2"(as the code"
created a Module "modDiff2Dates"(like you wrote)
created a query from table1

and putet the code in a blank column

SELECT [date1], [date2], Diff2Dates("ymd",[date1],[date2]) As Diff 
FROM [table1]

 

[Gasman]

Use the query designer

Date1 should be in one column
Date2 should be in another
Diff2Dates("ymd",[date1],[date2]) As Diff in another

 

[The_Doc_Man]

Well, there is this approach if the [Date1] and [Date2] fields are actually DATE format (as opposed to a string in the format of a "displayed date.") This approach I will use makes the assumption, which might be incorrect in your business rules, that for the purpose of this computation, months are 30 days each (because we are talking "short term" rates). It uses the fact that DATE format is actually stored as a floating point number when not being used in "displayed date" formats.

SELECT [Date1], [Date2], INT( ( [Date2] - [Date1] ) / 30 ) As Months, INT ( ( [Date2] - [Date1] ) MOD 30 ) As Days FROM [Table1] ;

This will give you full months as Months and leftover days as Days. I use the MOD operator, which if you didn't know about, can be found here:

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/mod-operator

IF you really wanted to get months based STRICTLY on calendar months (i.e. taking into account which ones have 30, 31, 28, or 29 (in leap year) then you would need special functions to compute this.

The Diff2Dates function showed earlier would tell you what you wanted to know except that you would have to parse the answer to split months from days. But it is a viable solution as well. I offered my little SQL code ONLY if you didn't like the Date2Diff function. I am not suggesting any error in the function, but as we well understand, it is a matter of getting what you want in the format you wanted it - so I offered an alternative.

 

[eshai]

Use the query designer

Date1 should be in one column
Date2 should be in another
Diff2Dates("ymd",[date1],[date2]) As Diff in another

Exactly what I wrote that I did

SELECT [Date1], [Date2], INT( ( [Date2] - [Date1] ) / 30 ) As Months, INT ( ( [Date2] - [Date1] ) MOD 30 ) As Days FROM [Table1] ;

same error

 

[isladogs]

@Eshai
I've prepared a simple working example for you
Table tblDates with StartDate & EndDate fields
Standard module modDateFunction with Diff2Dates function
Query qryDateDiffs:

SELECT tblDates.StartDate, tblDates.EndDate, Diff2Dates("ymd",[StartDate],[EndDate]) AS Diff
FROM tblDates;

Hopefully this will help you see what you've done differently and which is causing your errors

EDIT Just read your last post. It seems you're not using the Diff2Dates function after all
Each month does not contain 30 days.
Your expressions for Months & Days will not give correct answers unless they are in the same month or consecutive months where one has 30 days

 

[eshai]

@Eshai
I've prepared a simple working example for you
Table tblDates with StartDate & EndDate fields
Standard module modDateFunction with Diff2Dates function
Query qryDateDiffs:

SELECT tblDates.StartDate, tblDates.EndDate, Diff2Dates("ymd",[StartDate],[EndDate]) AS Diff
FROM tblDates;

Hopefully this will help you see what you've done differently and which is causing your errors

EDIT Just read your last post. It seems you're not using the Diff2Dates function after all

thank you very much isladogs

1 Q' can i turn it to a number "m"+"d" = 5.6(example) rong Q'

i need to multiplied the result by 1200$(for example)

 

[isladogs]

1 Q' can i turn it to a number "m"+"d" = 5.6(example)

Do you mean instead of e.g. 5 months 6 days you want the much more ambiguous 5.6?
And say 2 years, 3 months and 23 days would then be 2.3.23?
I think that would be VERY confusing.

EDIT:
Wrote the above before you edited your answer
If you are trying to calculate an amount due based on 5 months and 0.6 month, your problem again comes back to the fact that the number of days in a month isn't fixed
So instead I would recommend you have a daily amount due e.g. DayRateDue = $40

Then calculate AmountDue: Diff2Dates("d", EndDate, StartDate)*DayRateDue

 

[eshai]

Do you mean instead of e.g. 5 months 6 days you want the much more ambiguous 5.6?
And say 2 years, 3 months and 23 days would then be 2.3.23?
I think that would be VERY confusing

in the "diff field" i get the result as "1 year 1 months 1 days"(its a string as any sting not a date format ) can i Extract the number next to the text
exemple: extract the "1" from the "year"(it can be 2 numbers) to a new field in the query
same to the "months" and "days"

 

[isladogs]

Sorry but I'm getting confused by your questions which seem to keep changing.
Perhaps you could explain with an actual example or two what you want as your output

 

[Gasman]

Use Split() with a " " and test for how many entries you get, so you know whether you have year and month entries.

A daily rate would be an easy way out as isladogs mentioned?

Also how are you going to evaluate the cost of the days?, what would 7 or 9 days be?

 

[eshai]

Sorry but I'm getting confused by your questions which seem to keep changing.
Perhaps you could explain with an actual example or two what you want as your output

Also how are you going to evaluate the cost of the days?, what would 7 or 9 days be?

i get the result as "1 year 1 months 1 day"(example)
now i have a field that show me what a person should pay for a month(Varies from person to person) a month will always by divide by 30(Bookkeeping)
so I will take the year and divide it by 12, multiplied by the monthly amount
then the months multiplied by the monthly amount
and the days = monthly amount divided by 30 multiplied the days
in the and the formula is "year amount+months amount+days amount"

You're right, I can do everything by days but I have a problem with the bookkeeping department