Calculate difference between dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months from a
date in a separate field?
2. How do I calculate the number of months an employee has been working with
a company. (I have a field for the StartDate)?
 
Okay I've been able to use the information received between the two separate
posts to get the some of the information completed by using
DateSerial(Year([StartDate]),Month([StartDate])+12,Day([StartDate])) I've
been able to calculate six months and one year (bya change the 12 to a 6 for
six months) from the start date. But its the last issue that has me stumped.

Employee benefits start on the first of the
month after the date of hire if an employee is hired before the 15th of the
month and the first of the following month if hired after the 15th. Is this
possible?

Referring to the link provide in my other post help partially answer this
post but not the problem above. HELP
 
Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months from a
date in a separate field?
Click to expand...

Don't store this value in any table. Calculate it on the fly as
needed.
2. How do I calculate the number of months an employee has been working with
a company. (I have a field for the StartDate)?
Click to expand...

See the suggestions regarding DateAdd() and DateDiff() that Brendan
posted.

John W. Vinson[MVP]
 
I've gone over the information on Brendan's link, I guess my problem is
trying to figure out the right way to write the Iff statement to distinguish
between people hire before the 15th and people hired after.
 
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]), IIf(Day([HireDate]) <
15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002
 
Um, no sorry, looking at my results more closely that's clearly not right.
Doesn't handle the roll-over into the next year correctly. I'm going
off-line now, I'll try to look at it again tomorrow unless someone else
comes up with a solution before then.

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]), IIf(Day([HireDate])
< 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002
Click to expand...
 
simple, but brilliant

Pieter

you might want to add a simple explenation for the IIf though, for the not
so scientific
ie 'for people hired before the 15th . use currrent month, else next month'
Purely educational <g>


Brendan Reynolds said:
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]), IIf(Day([HireDate])
< 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002
Click to expand...
 
works like a charm

Pieter


Brendan Reynolds said:
Um, no sorry, looking at my results more closely that's clearly not right.
Doesn't handle the roll-over into the next year correctly. I'm going
off-line now, I'll try to look at it again tomorrow unless someone else
comes up with a solution before then.

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]), IIf(Day([HireDate])
< 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002

--
Brendan Reynolds
Access MVP

Lori said:
I've gone over the information on Brendan's link, I guess my problem is
trying to figure out the right way to write the Iff statement to
distinguish
between people hire before the 15th and people hired after.
--
Lori A. Pong


:

Use the DateAdd and DateDiff functions. There are examples and links to
further information at the following URL ...

http://office.microsoft.com/en-gb/assistance/HA010546621033.aspx#b60

--
Brendan Reynolds
Access MVP

Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months
from
a
date in a separate field?
2. How do I calculate the number of months an employee has been
working
with
a company. (I have a field for the StartDate)?
Click to expand...
Click to expand...
Click to expand...
 
You're not adding anything to the current month: the month in your
DateSerial function is strictly IIf(Day([HireDate]) < 15, 1, 2). That should
be Month([HireDate]) + IIf(Day([HireDate]) < 15, 1, 2)

(Would have been more obvious is you hadn't used December and January dates
for your test)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brendan Reynolds said:
Um, no sorry, looking at my results more closely that's clearly not right.
Doesn't handle the roll-over into the next year correctly. I'm going
off-line now, I'll try to look at it again tomorrow unless someone else
comes up with a solution before then.

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]), IIf(Day([HireDate])
< 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002

--
Brendan Reynolds
Access MVP

Lori said:
I've gone over the information on Brendan's link, I guess my problem is
trying to figure out the right way to write the Iff statement to
distinguish
between people hire before the 15th and people hired after.
--
Lori A. Pong


:

Use the DateAdd and DateDiff functions. There are examples and links to
further information at the following URL ...

http://office.microsoft.com/en-gb/assistance/HA010546621033.aspx#b60

--
Brendan Reynolds
Access MVP

Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months
from
a
date in a separate field?
2. How do I calculate the number of months an employee has been
working
with
a company. (I have a field for the StartDate)?
Click to expand...
Click to expand...
Click to expand...
 
That's it, thanks Doug. The complete revised query looks like so ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]),Month([HireDate]) +
IIf(Day([HireDate]) < 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

.... giving the following results (dd/mm/yyyy format again) ...

HireDate BenefitDate
14/11/2001 01/12/2001
14/12/2001 01/01/2002
14/01/2002 01/02/2002
14/02/2002 01/03/2002
15/11/2001 01/01/2002
15/12/2001 01/02/2002
15/01/2002 01/03/2002
15/02/2002 01/04/2002
16/11/2001 01/01/2002
16/12/2001 01/02/2002
16/01/2002 01/03/2002
16/02/2002 01/04/2002

--
Brendan Reynolds
Access MVP

Douglas J. Steele said:
You're not adding anything to the current month: the month in your
DateSerial function is strictly IIf(Day([HireDate]) < 15, 1, 2). That
should be Month([HireDate]) + IIf(Day([HireDate]) < 15, 1, 2)

(Would have been more obvious is you hadn't used December and January
dates for your test)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brendan Reynolds said:
Um, no sorry, looking at my results more closely that's clearly not
right. Doesn't handle the roll-over into the next year correctly. I'm
going off-line now, I'll try to look at it again tomorrow unless someone
else comes up with a solution before then.

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]),
IIf(Day([HireDate]) < 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002

--
Brendan Reynolds
Access MVP

I've gone over the information on Brendan's link, I guess my problem is
trying to figure out the right way to write the Iff statement to
distinguish
between people hire before the 15th and people hired after.
--
Lori A. Pong


:

Use the DateAdd and DateDiff functions. There are examples and links
to
further information at the following URL ...

http://office.microsoft.com/en-gb/assistance/HA010546621033.aspx#b60

--
Brendan Reynolds
Access MVP

Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months
from
a
date in a separate field?
2. How do I calculate the number of months an employee has been
working
with
a company. (I have a field for the StartDate)?
Click to expand...
Click to expand...
Click to expand...
 
Perfect. Thanks for all the help. I've learned a lot about access in the past
but I've learned so much more from the folks on this site.
--
Lori A. Pong


Brendan Reynolds said:
That's it, thanks Doug. The complete revised query looks like so ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]),Month([HireDate]) +
IIf(Day([HireDate]) < 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

.... giving the following results (dd/mm/yyyy format again) ...

HireDate BenefitDate
14/11/2001 01/12/2001
14/12/2001 01/01/2002
14/01/2002 01/02/2002
14/02/2002 01/03/2002
15/11/2001 01/01/2002
15/12/2001 01/02/2002
15/01/2002 01/03/2002
15/02/2002 01/04/2002
16/11/2001 01/01/2002
16/12/2001 01/02/2002
16/01/2002 01/03/2002
16/02/2002 01/04/2002

--
Brendan Reynolds
Access MVP

Douglas J. Steele said:
You're not adding anything to the current month: the month in your
DateSerial function is strictly IIf(Day([HireDate]) < 15, 1, 2). That
should be Month([HireDate]) + IIf(Day([HireDate]) < 15, 1, 2)

(Would have been more obvious is you hadn't used December and January
dates for your test)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brendan Reynolds said:
Um, no sorry, looking at my results more closely that's clearly not
right. Doesn't handle the roll-over into the next year correctly. I'm
going off-line now, I'll try to look at it again tomorrow unless someone
else comes up with a solution before then.

--
Brendan Reynolds
Access MVP

If I understand the question correctly, I believe this should do it ...

SELECT tblTest.HireDate, DateSerial(Year([HireDate]),
IIf(Day([HireDate]) < 15, 1, 2), 1) AS BenefitDate
FROM tblTest;

Here are some sample results (dd/mm/yyyy format) ...

HireDate BenefitDate
14/12/2001 01/01/2001
14/01/2002 01/01/2002
15/12/2001 01/02/2001
15/01/2002 01/02/2002
16/12/2001 01/02/2001
16/01/2002 01/02/2002

--
Brendan Reynolds
Access MVP

I've gone over the information on Brendan's link, I guess my problem is
trying to figure out the right way to write the Iff statement to
distinguish
between people hire before the 15th and people hired after.
--
Lori A. Pong


:

Use the DateAdd and DateDiff functions. There are examples and links
to
further information at the following URL ...

http://office.microsoft.com/en-gb/assistance/HA010546621033.aspx#b60

--
Brendan Reynolds
Access MVP

Basically I need two things for a new database.
1. How do I get a query to automatically calculate a date six months
from
a
date in a separate field?
2. How do I calculate the number of months an employee has been
working
with
a company. (I have a field for the StartDate)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top