date calculaton

Respected experts I am trying to obtain number of years in a form ,I have two fields named date of joining of an employee and today's date .and when I subtract date of joining from today's date it returns I think number of days and not the number of years or months .your kind guidance is requested in this regard.thax

Well you didn't say how you were subtracting these dates, or the parameters/arguments used, so it is difficult to be specific.
However, google can be your friend with MS Access functions.
try "techonthenet msaccess date functions" techonthenet is an excellent resource
and specifically http://www.techonthenet.com/access/f...e/datediff.php
Good luck.

When dealing with this kind of calculations then thing that has to be decided, at the outset, is how much accuracy you're looking for. Orange has given you a reference for DateDiff(), the Access Function designed for doing this sort of thing, but used by itself, DateDiff() is very literal in what it returns.

Using 'yyyy,'the Interval argument for for 'years,' as in

DateDiff("yyyy", StartDate, EndDate)

is going to give you just that, the difference in years. Given

StartDate = December 31, 2012

EndDate = January 1, 2013

The code

YearsBetween = DateDiff("yyyy", StartDate, EndDate)

will return, for YearsBetween, the answer of 1 year! This, despite the fact that the two dates are, in actuality, only one day apart! That's because

2013-2012 =1

So to get a more accurate answer, you need to use DateDiff plus some other logic. The other logic involves parsing the number of days into whatever unit of time you want, i.e. weeks, months, years, etc.

And even with this other logic, the answer is never going to be 100% correct, unless you simply want the days or weeks between two dates[/B], because all months are not equal and all years are not equal.

Months can have 28, 29, 30 or 31 days.

Years can have 364 or 365 days.

Since weeks always have 7 days, parsing the return to weeks is simple, calculate the difference in days and divide by 7:

TotalWeeks = DateDiff("d", StartDate, EndDate)/7

If you only want Whole Weeks, i.e. no fraction of a week

TotalWeeks = DateDiff("d", StartDate, EndDate)\7

But after that it gets murky. So, as I said, you have to decide, up front, how accurate you need to be. Use DateDiff to calculate the number of days, and parse your desired unit of time from there.

If your interest here is to calculate the number of whole years a person's been with your company, taking into account whether they’ve passed their 'date of employment', for the current year, you'd do that the same way you'd calculate how 'old' a person is. Here are two commonly used expression for doing that:

Code:

DateDiff("yyyy", [DateOfEmployment], Date) - IIf(Format$(Date, "mmdd") < Format$([ DateOfEmployment], "mmdd"), 1, 0)

OR

Code:

 DateDiff("yyyy", [DateOfEmployment], Date()) +  Int( Format(Date(), "mmdd") < Format( [DateOfEmployment], "mmdd") )

Linq ;0)>

I use

Code:

Int(DateDiff("m",[DataPersons].[BirthDate],Now())/12)

to calculate a person's age in years. This calculates the date difference in months and then gives the years as the integer of a division by 12.
Change [BirthDate] to [DateOfEmployment] to give completed years of service.

HTH

There's almost always more than one way (or event two ways) to skin a cat!

It doesn't matter, in this case, but as a matter of policy, it's probably better to use Date(), rather than Now(), unless the Time component is specifically needed. Down the road, if you need to compare a literal date to a date value that was entered using Now(), the comparison will always fail, unless the Now() was entered at Midnight.

BTW, roaftech, I'll add this method to my ever growing collection! Always like to give a person a choice! Speaking of which, you could leave out the Int() function and just use

Code:

DateDiff("m", Me.BirthDate, Now()) \ 12

Changing the Slash into a Back Slash tells Access to only return the Integer portion of the division results.

Linq ;0)>

Thanks for the extra info - I'm always willing to learn something from an expert. My coding experience started in the mid-70s so some of the more modern syntax sometimes escapes me!

In return, you might like to review one of your earlier statements - "Years can have 364 or 365 days". In Europe we use 365 and 366 !

Of all the things I miss, I miss my mind the most!

The silly thing is, I had to go and ask several other people to check!!

It's nice to have company on the way to La La Land!

Hello again after 4 years.Being a novice here I did not know much about the forum rules and regulations or we can say the culture. I saw the solutions and applied all ov''em one by one. All worked fined at that time. I missed to thank. Now when I am aware of the culture I must say

"THANK YOU SO VERY MUCH FOR ALL YOUR PRECIOUS TIME TO HELP ME OUT, INDEED."

You're very welcome, imranbhatti!

Linq ;0)>