Years of Service Calculation to 2 decimal places

  • Thread starter Thread starter forest8
  • Start date Start date
F

forest8

Hi there

In my database, I have used the following calculation to determine a
person's years of service.

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

A t the moment, the result is rounded to the nearest whole number.

I would like to see at least 2 decimal places. At the moment, if someone has
been with the company less than 1 year, I get 0 years. I would like to see if
it's .75 years or .5 years, etc.

Thank you in advance for your help.
 
forest8,

Under properties format the field on you form to fixed = 2 (if you want to
always have to decimal places) or Format = Standard and set Decimal Places
to 2

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi there

In my database, I have used the following calculation to determine a
person's years of service.

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

A t the moment, the result is rounded to the nearest whole number.

I would like to see at least 2 decimal places. At the moment, if someone has
been with the company less than 1 year, I get 0 years. I would like to see
if
it's .75 years or .5 years, etc.

Thank you in advance for your help.
 
Hi,

DateDiff("yyyy",...) always returns a whole number of years (which is why
you have to subtract one if the HireDate is greater than the current date).

So to get a fraction of year, you'll have to use:
YearsOfService: DateDiff("m",",[HireDate],Date()) / 12

You can then set the format to display as many or as few decimal places as
you like.

Cheers and HTH,
Alex.
 
Hi

Will I have a problem if this person no longer works in my company?

There is an Exit Date in my database.

Tokyo Alex said:
Hi,

DateDiff("yyyy",...) always returns a whole number of years (which is why
you have to subtract one if the HireDate is greater than the current date).

So to get a fraction of year, you'll have to use:
YearsOfService: DateDiff("m",",[HireDate],Date()) / 12

You can then set the format to display as many or as few decimal places as
you like.

Cheers and HTH,
Alex.


forest8 said:
Hi there

In my database, I have used the following calculation to determine a
person's years of service.

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

A t the moment, the result is rounded to the nearest whole number.

I would like to see at least 2 decimal places. At the moment, if someone has
been with the company less than 1 year, I get 0 years. I would like to see if
it's .75 years or .5 years, etc.

Thank you in advance for your help.
Click to expand...
Click to expand...
 
Hi,

No, you shouldn't have a problem. Simply replace the "Date()" in the
expression with "[ExitDate]".

Cheers,
Alex.


forest8 said:
Hi

Will I have a problem if this person no longer works in my company?

There is an Exit Date in my database.

Tokyo Alex said:
Hi,

DateDiff("yyyy",...) always returns a whole number of years (which is why
you have to subtract one if the HireDate is greater than the current date).

So to get a fraction of year, you'll have to use:
YearsOfService: DateDiff("m",",[HireDate],Date()) / 12

You can then set the format to display as many or as few decimal places as
you like.

Cheers and HTH,
Alex.


forest8 said:
Hi there

In my database, I have used the following calculation to determine a
person's years of service.

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

A t the moment, the result is rounded to the nearest whole number.

I would like to see at least 2 decimal places. At the moment, if someone has
been with the company less than 1 year, I get 0 years. I would like to see if
it's .75 years or .5 years, etc.

Thank you in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Will I have a problem if this person no longer works in my company?
Click to expand...

You can get finer granularity and handle both current and fromer employees
with:

YearsOfService: Round(DateDiff("d",",[HireDate],NZ([ExitDate],Date())) /
365,2)
 
forest8 said:
Hi there

In my database, I have used the following calculation to determine a
person's years of service.

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

A t the moment, the result is rounded to the nearest whole number.

I would like to see at least 2 decimal places. At the moment, if someone
has
been with the company less than 1 year, I get 0 years. I would like to see
if
it's .75 years or .5 years, etc.

Thank you in advance for your help.
Click to expand...
 
Back
Top