Calculating date hired

[mybers]

Hello everyone,

I dont know if my this question has been posted before...

I'd like to calculate the employees date hired to the present date. and be able to output the ff:

1. Years in the company
2. Months in the company, including DAY (for probationary
employees)
3. months/Days left since the datehired to present date

I hope anyone can enlighten me on this one...

muchas gracias

mybers

 

[BobJacksonNCI]

From Microsoft Knowledgebase article 210276:

To calculate and format time intervals correctly, follow these steps:
1. Create a module and type the following line in the Declarations section if it is not already there:
2. Option Explicit
3. Type the following procedure:
4. '------------------------------------------------------------------
5. ' This function calculates the elapsed time between two values and
6. ' formats the result in four different ways.
7. '
8. ' The function accepts interval arguments such as the following:
9. '
10. ' #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
11. '
12. ' -or-
13. '
14. ' [End Time]-[Start Time]
15. '------------------------------------------------------------------
16.
17. Function ElapsedTime (Interval)
18. Dim x
19. x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
20. Debug.Print x
21. x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
22. & " Minutes:Seconds"
23. Debug.Print x
24. x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
25. & " Hours:Minutes:Seconds"
26. Debug.Print x
27. x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
28. & " Hours " & Format(Interval, "nn") & " Minutes " & _
29. Format(Interval, "ss") & " Seconds"
30. Debug.Print x
31.
32. End Function

33. Type the following line in the Immediate window, and then press ENTER:
? ElapsedTime(#6/1/1999 8:23:00PM#-#6/1/1999 8:12:12AM#)
Note that the following values are displayed:
43848 Seconds
730:48 Minutes:Seconds
12:10:48 Hours:Minutes:Seconds
0 days 12 Hours 10 Minutes 48 Seconds


HTH,
Bob

 

[BobJacksonNCI]

Geesh!

Wish I could UNpost that!

You'll need to use DateDiff with Format.

Will give you a better (MUCH better) response, if no one else does.

Sorry!
Bob

 

[BobJacksonNCI]

1. Years in the company

YrsWithCo=DateDiff("yyyy",[HireDate],Date())+Int(Format(Date(),"mmdd")<Format([HireDate],"mmdd"))

Note that DateDiff "yyyy" gives whole years and must be adjusted (-1) when the anniversary has not passed.


2. Months in the company, including DAY (for probationary employees).

My solution is MESSY, but fits on one line. A cleaner solution can be found on The Access Web, but requires creating a function. See

http://www.mvps.org/access/datetime/date0001.htm

MonthsDays=DateDiff("m",[HireDate],Date()) & " Months and " & DateDiff("d",DateAdd("m",DateDiff("m",[HireDate],Date()),[HireDate]),Date()) & " Days"


3. months/Days left since the datehired to present date[/-b]
Sorry, I don't understand what you need.


HTH,
Bob

 

[MichaelRed]

Many variations of this 'theme' have been asked and answered directly in these fora (Tek-Tips). Most are in the aligned vategory of determining the age from the date of birth, and I would suggest "Age" and "DOB" AND "DateDif" as ssearch terms. Be aware that some espouse the simple division by some value (365.24 or 365.25) which are, while widely used, are inaccurate in the extremes of the range of calculation.





MichaelRed
mlred@verizon.net

 

[mybers]

Hi Bob

Sorry for not being too elaborative on this...

The formula seems to be okay however I got entangled with this:

If the datehired= 2/27/1996 then YearsMonthsDays(date() stay= 8 years and 0 months and 2 days

but what happens is 7 and 96 Months and -2 Days which is wrong

It should be like this Years and months and days employed. based on the present date

How can I reformulate this?

Thanks

mybers

 

[BobJacksonNCI]

mybers,

I thought I had it right, but did not test enough. It could be done on one line, but not recommended. The problem with months and days is the same as the one with years - an expression that is correct if the month/day anniversary has passed is not correct when the month/day anniversary has not passed. It would be possible to:

IIF(Format([HireDate],"mmdd")<Format(date(),"mmdd" + paste my previous post , repeat post with adjustment for anniversary not passed)

But I wouldn't do it that way. So, it's best to create a function for this. An excellent example was provided by MichaelRed:

thread701-498444



HTH,
Bob