MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Years of Service Calculation to 2 decimal places

• Follow

```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.

```
 0

```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

"forest8" <forest8@discussions.microsoft.com> wrote in message
news:540F2F2A-5D02-45A4-9BC3-8C4C0F112987@microsoft.com...
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.

```
 0

```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" wrote:

> 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.
>
```
 0

```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" wrote:

> 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" wrote:
>
> > 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.
> >
```
 0

```Hi,

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

Cheers,
Alex.

"forest8" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > >
```
 0

```On Sun, 14 Mar 2010 22:05:01 -0700, forest8
<forest8@discussions.microsoft.com> wrote:

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

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

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

--

John W. Vinson [MVP]

```
 0

```Jhall@myglnc.com
"forest8" <forest8@discussions.microsoft.com> wrote in message
news:540F2F2A-5D02-45A4-9BC3-8C4C0F112987@microsoft.com...
> 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.
>

```
 0

```"forest8" <forest8@discussions.microsoft.com> wrote in message
news:540F2F2A-5D02-45A4-9BC3-8C4C0F112987@microsoft.com...
> 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.
>

```
 0

7 Replies
468 Views

Similiar Articles:

7/20/2012 2:46:19 AM