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
Please help
-
-
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. -
Thank You!!! I had been bangin my head on this!!Comment
-
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)>Comment
-
Thanks Ling. I really appreciate when people take the time to teach, which is why I am here.
DanComment
-
You're quite welcome, Dan! It's why we're here, too!
Linq ;0)>Comment
-
RATS!!!
I was sure it (DateDiff) returned the number of full years based on the full date :(
I shall have to remember that one.Comment
Comment