By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,050 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.


P: 147
How do I get datediff to give me an accurate count of years. if I enter a date of November 2, 2008 it shows me 1 year as of Today instead of zero. How do I make it count on the actual anniversary?????

Please help
Jan 20 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,923
Anniversary: DateDiff("yyyy", [AnniversaryDate], Now())+ Int( Format(Now(), "mmdd") < Format( [AnniversaryDate], "mmdd") )
__________________________________________________ __________
Here is how it works:

Anniversary will be the number of years from the DateDiff calculation plus 0, if we have passed the anniversary day already, or plus negative 1 (-1), if the anniversary day is still in the future.

DateDiff("yyyy", [AnniversaryDate], Now()) calculates the difference between the year of the anniversary date and the current year. This is all you would need if you KNEW that the anniversary day for this year had already passed.

Format(Now(), "mmdd") returns the month and day for today's date.

Format( [AnniversaryDate], "mmdd") returns the month and day for the anniversary date

Format(Now(), "mmdd") < Format( [AnniversaryDate], "mmdd") asks if the month and day for today is before the month and day in the anniversary date.

Int(expression) The Int function returns the integer part of a number. In this case we are using the Int function to get a number (Boolean 0 = False , -1 = True) out of a logical comparison.

Example: if today is January 20, 2009 and the anniversary date is May 25, 1972, we are looking at the question "Is January 20 < May 25", which is True.
So when today's date is before the anniversary date, the Int part is -1 and the full expression for Anniversary adds -1 to the DateDiff. This gives the correct anniversary, as of today. As soon as Now() gets to May 25, the Int part of the expression evaluates to 0 and the DateDiff result is the correct anniversary on that date.
Jan 20 '09 #2

P: 147
Thank You!!! I had been bangin my head on this!!
Jan 21 '09 #3

Expert 2.5K+
P: 3,532
PDB's solution, with its excellent explanation, is the standard way of determining a person's actual age at any giving point in time.. In general, there's two things you have to keep in mind when using the DateDiff() function:

  1. DateDiff() always returns the difference of the interval you specified
  2. How accurate a date difference do you need?
As you found out, if you specify "yyyy" as the Interval argument, Access will return the difference in the year portion of the two dates. If you use year as your Interval, then 12/31/2008 and 1/1/2009 will return a difference in years of 1, even though, in actuality, it's only a difference of 1 in days!. In other words, Access gives you what asked for!

As I said, you also have to think about the accuracy you need. Using the dates

and 12/31/2008

and calculating the difference in months

will yield 1, the difference, in months, between 11/2008 and 12/2008.

Using the same dates but calculating the difference in days

will yield 60, which most people would consider to be 2 months, not 1 month!

To work around this, you need to use DateDiff() with the lowest common denominator, if you will, that will give you the accuracy you need, then divide it by your definition of the unit of date or time. So if you define a month as 30 days, to determine the difference, in months, between a StartDate of 11/1/2008 and an EndDate of 12/31/2008 you would do this:

Months = DateDiff("d", StartDate, EndDate)/30

which will yield 2 months.
Linq ;0)>
Jan 21 '09 #4

P: 147
Thanks Ling. I really appreciate when people take the time to teach, which is why I am here.
Jan 21 '09 #5

Expert 2.5K+
P: 3,532
You're quite welcome, Dan! It's why we're here, too!

Linq ;0)>
Jan 22 '09 #6

Expert Mod 15k+
P: 31,494

I was sure it (DateDiff) returned the number of full years based on the full date :(

I shall have to remember that one.
Jan 26 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.