Date Difference

[Guest]

In a report I am calculating the date difference between Date Hired and 'now'
to show Length of Service using the following expression:

=DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"ddmmyy")<Format([DateHired],"ddmmyy"))

My problem is that I have one gent who started on 20-Jan-2003. When the
report runs it puts his Length of Service as 3... however the very next line
has someone who started on 28-Jan-2003 and the report says their Length of
Service is 2

There are a couple of instances of this same problem throughout the
report... and I can't figure out what is going on. I've tried changing the
dates and if I take the 28th start date back a couple of days to say the 27th
it will show the correct length of time...

What on earth is going on?!

 

[Allen Browne]

If you are trying to calculate the whole number of completed years of
service, grab the function from this link:
http://allenbrowne.com/func-08.html
and then use:
=Age([DateHired])

Since you seem to be working in a country that uses the d/m/y format, you
may also get some help from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Guest]

Eeek! Where do I put all that code? In the Control Source box in the
Expression Builder? (You're dealing with a new user in WAY over her head)!

Thanks Allen

If you are trying to calculate the whole number of completed years of
service, grab the function from this link:
http://allenbrowne.com/func-08.html
and then use:
=Age([DateHired])

Since you seem to be working in a country that uses the d/m/y format, you
may also get some help from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In a report I am calculating the date difference between Date Hired and
'now'
to show Length of Service using the following expression:

=DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"ddmmyy")<Format([DateHired],"ddmmyy"))

My problem is that I have one gent who started on 20-Jan-2003. When the
report runs it puts his Length of Service as 3... however the very next
line
has someone who started on 28-Jan-2003 and the report says their Length of
Service is 2

There are a couple of instances of this same problem throughout the
report... and I can't figure out what is going on. I've tried changing
the
dates and if I take the 28th start date back a couple of days to say the
27th
it will show the correct length of time...

What on earth is going on?!

 

[Allen Browne]

1. Select the Modules tab of the database window, and click New.

2. Paste the code in.

3. Save it with a name such as Module1.

You can then put this into the Control Source of your text box:
=Age([DateHired])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Eeek! Where do I put all that code? In the Control Source box in the
Expression Builder? (You're dealing with a new user in WAY over her
head)!

Thanks Allen

If you are trying to calculate the whole number of completed years of
service, grab the function from this link:
http://allenbrowne.com/func-08.html
and then use:
=Age([DateHired])

Since you seem to be working in a country that uses the d/m/y format, you
may also get some help from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

In a report I am calculating the date difference between Date Hired and
'now'
to show Length of Service using the following expression:

=DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"ddmmyy")<Format([DateHired],"ddmmyy"))

My problem is that I have one gent who started on 20-Jan-2003. When
the
report runs it puts his Length of Service as 3... however the very next
line
has someone who started on 28-Jan-2003 and the report says their Length
of
Service is 2

There are a couple of instances of this same problem throughout the
report... and I can't figure out what is going on. I've tried changing
the
dates and if I take the 28th start date back a couple of days to say
the
27th
it will show the correct length of time...

What on earth is going on?!

 

[Guest]

Thank you, thank you! Would never in a million years have worked that one
out for myself!

1. Select the Modules tab of the database window, and click New.

2. Paste the code in.

3. Save it with a name such as Module1.

You can then put this into the Control Source of your text box:
=Age([DateHired])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Eeek! Where do I put all that code? In the Control Source box in the
Expression Builder? (You're dealing with a new user in WAY over her
head)!

Thanks Allen

If you are trying to calculate the whole number of completed years of
service, grab the function from this link:
http://allenbrowne.com/func-08.html
and then use:
=Age([DateHired])

Since you seem to be working in a country that uses the d/m/y format, you
may also get some help from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

In a report I am calculating the date difference between Date Hired and
'now'
to show Length of Service using the following expression:

=DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"ddmmyy")<Format([DateHired],"ddmmyy"))

My problem is that I have one gent who started on 20-Jan-2003. When
the
report runs it puts his Length of Service as 3... however the very next
line
has someone who started on 28-Jan-2003 and the report says their Length
of
Service is 2

There are a couple of instances of this same problem throughout the
report... and I can't figure out what is going on. I've tried changing
the
dates and if I take the 28th start date back a couple of days to say
the
27th
it will show the correct length of time...

What on earth is going on?!

 

[syrena]

Dear Allen,
Thank you for the code- it works great...but how do now store the
calculated amount of years in the underlying table?

Again, another beginner..so please not too technical!

Thanks again