Year,Month,Day Calculation

[dmyoungsal]

I am working a "form" that will allow our users to do some simple onscreen calculations. (I am using Textboxes - I know, i know - for now this is the route I want to take)

I have a Start Date = 7/2/2016
I have an End Date = 9/20/2016

Using =DateDiff("yyyy",[txtStart],[txtEnd]) - I get the difference in Year
Using =DateDiff("m",[txtStart],[txtEnd]) - I get the difference on Month
Using =DateDiff("d",[txtStart],[txtEnd]) - I get all the days (not the 18 I am expecting)

suggestions?

 

[Ranman256]

Why do you want the difference in years?
Most people just want the days elapsed.

 

[Minty]

Why would you expect to get 18 in the days difference ? its 80 ? Puzzled.

 

[Grumm]

Your code works fine. It's just that the difference between 7/2/2016 and 9/20/2016 is 2 month or 80 days.
But maybe you want the difference in total. Like 0 year, 2 month and 18 days ?

 

[dmyoungsal]

Your code works fine. It's just that the difference between 7/2/2016 and 9/20/2016 is 2 month or 80 days.
But maybe you want the difference in total. Like 0 year, 2 month and 18 days ?

"Yes", exactly. Like 0 year, 2 month and 18 days

 

[Minty]

Actually - what is you definition of a year and a month in this context... I don't think there is a correct answer to your question.

 

[dmyoungsal]

Actually - what is you definition of a year and a month in this context... I don't think there is a correct answer to your question.

i am just trying to get the difference between the numbers in the given date fields. (9 (sept) - July (7) = 2), "/20/ - /2/" = 18.

Datediff seems to handle the year fine. (12/15/2015 - 1/30/2016) will show "0" for year and report 1 for month. If the starting year was 2014, it would show 1 year and one month.

 

[Minty]

Except it doesn't.
Type ?datediff("yyyy",#04/23/2015#, #04/22/2016#) into the immediate window.
Gives you a result of 1 which is incorrect. There isn't a whole year between these two dates.

 

[dmyoungsal]

Except it doesn't.
Type ?datediff("yyyy",#04/23/2015#, #04/22/2016#) into the immediate window.
Gives you a result of 1 which is incorrect. There isn't a whole year between these two dates.

I see what you say... however...

I am as concerned about the year as I am getting the “days”

I am trying to get the result that Excel gives me.

If I enter “12/1/2015” and “1/18/2016”, Excel’s DateDif gives me the results I need.
0 for year (=DATEDIF(C$19,D$19,"y"))
1 for month (=DATEDIF(C$19,D$19,"ym"))
17 for days (=DATEDIF($C$19,$D$19,"md"))

This is what I am trying to recreate in Access

 

[Minty]

It's not very simple - have a look at this thread http://stackoverflow.com/questions/...-in-years-and-months-in-microsoft-access-2010 for some of the interesting issues.

 

[missinglinq]

When doing this kind of thing, you have to calculate the difference, in the lowest common denominator (in this case it would be days) between two points in the data, and then parse it out to find, in this case Years, Months and Days) but therein lies the problem! Doing the difference in distance, say Inches, Feet, Yards and Miles is a snap, because each component is fixed, i.e. 12 Inches/Foot, 3 Feet/Yard, etc. But with DateTime...you have the problem of how many Days are in a Month? And the answer is 28 or 29 or 30 or 31...in other words, not fixed!

Graham Seach and Doug Steele developed a 'better' DateDiff() function, named Diff2Dates(), that does just what you want:

http://www.accessmvp.com/djsteele/Diff2Dates.html

Linq ;0)>

 

[dmyoungsal]

I figured out a solution. below is the code:

=DateDiff("d",[txtStart],[txtEnd])-([txtMonth]*30) (Because I had already determined months, I referred to that text box.)

Not glamorous, but pretty functional