435,050 Members | 1,564 Online
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.

# DateDiff

 100+ 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
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

 100+ 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: DateDiff() always returns the difference of the interval you specified 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 11/1/2008 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

 100+ P: 147 Thanks Ling. I really appreciate when people take the time to teach, which is why I am here. Dan 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 RATS!!! 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