Date Calculation for years of Service

  • Thread starter Thread starter DWillis
  • Start date Start date
D

DWillis

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
 
If you don't have to be really accurate, as in being a couple of days off
wouldn't hurt:

(Date()-[hire date])/365.5
 
It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
 
Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...

A value of 3770 (in days, which is what your expression calls for) is
about 10 years 4 months.
Access is most likely correctly performing the task you gave it.

To accurately calculate the **Years** of Service, you would use,
directly as the control source of an unbound control on a form or in a
report:

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

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.
 
My report displays #error in the field when I use this. Is there something
else I need to change? Thank you.
 
I receive these two results: -- With DateDiff("m",Date(),[hire
date])/12, I receive "enter parameter value (for the hire date).
Click to expand...

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis


KARL DEWEY said:
It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...
Click to expand...
 
Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.
--
DWillis


fredg said:
Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...

A value of 3770 (in days, which is what your expression calls for) is
about 10 years 4 months.
Access is most likely correctly performing the task you gave it.

To accurately calculate the **Years** of Service, you would use,
directly as the control source of an unbound control on a form or in a
report:

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

You do know, I hope, that this Age computation should NOT be stored
in any table. Just compute it and display it on a form or report, as
needed.
Click to expand...
 
Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
I receive these two results: -- With DateDiff("m",Date(),[hire
date])/12, I receive "enter parameter value (for the hire date).
Click to expand...

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis


KARL DEWEY said:
It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.
Click to expand...

Where are you putting the code? In your original post you indicated that there
was a field named [hire date]:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date].

Is that in fact the fieldname in this query, or is the field named something
else? Remember - YOU can see your database; we cannot!
 
Hello,
I am being asked to enter a parameter for the field [Hire Date] when I use
the code you provided.
Click to expand...

Do you have a field named "Hire Date"? Evidently not.
Note I used "HireDate" (without the space).
Anyway, replace [Hire Date] or [HireDate] with whatever the actual
name of the field is that contains the date the employee was hired.
make sure it's spelled correctly.

Also make sure the name of this control is not the same as the name of
any field used in it's control source expression.
 
Hello,

Yes this works better. The result for years appears exactly as of today or
whenever I run the report. However, is there a way to forecast ahead so I
could run the report for 2008, and the manager would see one whole number,
for instance, that in August, Joe Smith will be here for 11 years, and we
wouldn't have to round up the number?
Thank you again.--

DWillis


KARL DEWEY said:
Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
I receive these two results: -- With DateDiff("m",Date(),[hire
date])/12, I receive "enter parameter value (for the hire date).
Click to expand...

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis


KARL DEWEY said:
It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...
Click to expand...
Click to expand...
 
You can replace Date() with a prompt for a date --- [Enter as of date
- 08/22/2007] and it will be be based on that date entered.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
Hello,

Yes this works better. The result for years appears exactly as of today or
whenever I run the report. However, is there a way to forecast ahead so I
could run the report for 2008, and the manager would see one whole number,
for instance, that in August, Joe Smith will be here for 11 years, and we
wouldn't have to round up the number?
Thank you again.--

DWillis


KARL DEWEY said:
Check your typing. Your feedback post has a 'greater than' sign. The 'hire
date' is the name of your field and needs to match it axactly.
To change from the negative number use (DateDiff("d",[hire date],Date())/
365.25 )
Format your display for whatever precision you want.
--
KARL DEWEY
Build a little - Test a little


DWillis said:
I receive these two results: -- With DateDiff("m",Date(),[hire
date])/12, I receive "enter parameter value (for the hire date).

The 2nd set of code you gave me (DateDiff("d",Date(),[hire date])/365.25 )
is closest however it produces a negative number that is out greater than 10
decimal places. The number is right, but I don't need all the extra on the
report. How to fix?
Thank you,

DWillis


:

It is in days.
Use DateDiff("yyyyy",Date(),[hire date])

BUT, if the hire date was 23 April 2008 it will give you 1 year. If the
hire date was 3 May 2007 it will give you 0 year.

So to get better results you can use DateDiff("m",Date(),[hire
date])/12 or DateDiff("d",Date(),[hire date])/365.25 and so forth.

Search this newsgroup for Age, anniversaries, and birthdays for more ideas.
--
KARL DEWEY
Build a little - Test a little


:

Attempting to produce a number for Years of Service, by subtracting the field
[hire date]. I am using the following in an unbound text box in a report:
=Date()-[hire date]. It is producing a result, however not recognizeable. I
have the txt box formatted as a general number, so I am getting 3770,
(numbers that don't make sense in actual years). Thoughts?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hello happy new year to all, Text box input for year of purchase 0
Calculate Years of Service 11
Date Differences in Years 3
Access 2 Digit year in Access Text Box 3
Reporting on Year Only 2
calculate two dates 1
Calculating time between mulitple dates 4
Years of Employment 2

Back
Top