By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

DateDiff Fuction

P: n/a
I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:

These are the fields:

YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2

In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.

I have tried simply to add the two other fields to the code below, but
it
does not work:

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2])

I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].

This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?

Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
>
=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)

From MS Access 2000 Help Files:
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function only allows for calculating of _one_ pair of dates at
a time. So to remedy your problem I would do the following:

<aircode>
=(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2]))
</aircode>

Keep in mind that you could get rounding errors of up to 2 years using such
large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.

<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>

This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.

I would give the help file a read on DateDiff, it has some very useful
information for that function.

HTH

Mike Krous
"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:

These are the fields:

YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2

In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.

I have tried simply to add the two other fields to the code below, but
it
does not work:

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)
I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].

This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?

Thanks.

Nov 12 '05 #2

P: n/a
Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix() is just one of many that my suite your formating needs,
maybe Int() or Format() also.

something like:

=Fix(rest of formula here)

Mike Krous
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:Y8********************@comcast.com...

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )

From MS Access 2000 Help Files:
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function only allows for calculating of _one_ pair of dates at a time. So to remedy your problem I would do the following:

<aircode>
=(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2]))
</aircode>

Keep in mind that you could get rounding errors of up to 2 years using such large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.

<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>

This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.

I would give the help file a read on DateDiff, it has some very useful
information for that function.

HTH

Mike Krous
"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:

These are the fields:

YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2

In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.

I have tried simply to add the two other fields to the code below, but
it
does not work:

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] )

I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].

This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?

Thanks.


Nov 12 '05 #3

P: n/a
Thanks Mike, I am using yuor second code (/365) and works fine. There
is by the way still one thing that I would like to ask.
It may happen that sometimes I will not complete the STARTINGDATE2 and
ENDINGDATE2 fields but just the first two ones therofore I would like
my YEARS field to be able to sum only just the first two fields.

The way I wanted is that the YEARS field should always show the date
difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I
will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the
difference should be summed to the first result.

Is this possible? Thanks
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message news:<ZP********************@comcast.com>...
Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix() is just one of many that my suite your formating needs,
maybe Int() or Format() also.

something like:

=Fix(rest of formula here)

Mike Krous
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:Y8********************@comcast.com...

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)

From MS Access 2000 Help Files:
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function only allows for calculating of _one_ pair of dates

at
a time. So to remedy your problem I would do the following:

<aircode>
=(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2]))
</aircode>

Keep in mind that you could get rounding errors of up to 2 years using

such
large units of mesure on each end of the + symbol. Here is an alternative
solution that should yield in a smaller round error.

<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>

This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of
days together we then divide by the number of days in a year (365) to get
the number of years.

I would give the help file a read on DateDiff, it has some very useful
information for that function.

HTH

Mike Krous
"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
I am having some problem with a Year Function.
I have form on which I have 4 field which indicate dates and an
additional form which sums those dates:

These are the fields:

YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2

In each of DATE fields, I wil be adding a date. The YEARS field
should sum the values I enter in the other 4 fields.

I have tried simply to add the two other fields to the code below, but
it
does not work:

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)

I have also tried to use two other not visible fields, named A and, A1
and fill them with your origianal code and then add in the YEARS
field: =[A]+[A1].

This seems some solution, the only problem is that it may happen that
sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When
this happens, it does not make to calculation. If these fields do show
dates, the calculations is processed.
Any further help?

Thanks.


Nov 12 '05 #4

P: n/a
Paolo-
yes this is totally possible. In my post I said to use the following:
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
In order to seperate that information better I would do the following: In
your query I would seperate the formula above into two formulas then add
them together later.

column1 - get diff of starting date1, and ending date1, wrapped the function
inside of a Nz() function, the Nz() function will make sure that I at least
get a 0 for my calculation if for some odd reason I would have gotten a
null. This will make sure Ive got something to add to later.
=Nz(DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]),0)

column2 - get the diff of starting date 2 and ending date 2.
=Nz(DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]),0)

column3 - add column 1 and column 2 together then divide by 365 to get total
years.
=((column1 + column2) / 365)

Note: columns 1 and 2 are still in day format. when you display them in a
control you could use the following to get back to years:
=column1 / 365 or column2 / 365
everytime you see column1 and column2 you will have to replace the words
"column1" and "column2" with your actual names. Alternatively you could get
the time in years from start1 end1 and start2 end2 by using the following
additional columns in your query.

column4
=column1 / 365

column5
=column2 / 365

then you can simply place column 4 and 5 into your form without any
calculations.

now with the above columns you have everything broke out into pieces.
Column1 holds the difference between start1 and end1 and column2 holds the
difference between start2 and end2, column3 simply has the total between the
two differences divided by 365 to get years. The added columns 4 and 5
simply take columns 1 and 2 and bring them down into years.

HTH

Mike Krous
"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om... Thanks Mike, I am using yuor second code (/365) and works fine. There
is by the way still one thing that I would like to ask.
It may happen that sometimes I will not complete the STARTINGDATE2 and
ENDINGDATE2 fields but just the first two ones therofore I would like
my YEARS field to be able to sum only just the first two fields.

The way I wanted is that the YEARS field should always show the date
difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I
will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the
difference should be summed to the first result.

Is this possible? Thanks
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message

news:<ZP********************@comcast.com>...
Note:
If you use something like the second method I suggested, you may have to
wrap the whole function inside the Fix() function to get rid of the
decimal....Fix() is just one of many that my suite your formating needs,
maybe Int() or Format() also.

something like:

=Fix(rest of formula here)

Mike Krous
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:Y8********************@comcast.com...
>

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)

From MS Access 2000 Help Files:
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The DateDiff function only allows for calculating of _one_ pair of dates
at
a time. So to remedy your problem I would do the following:

<aircode>
=(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2]))
</aircode>

Keep in mind that you could get rounding errors of up to 2 years using

such
large units of mesure on each end of the + symbol. Here is an
alternative solution that should yield in a smaller round error.

<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>

This second piece of code calculates the difference in days, leaving
rounding errors of a day or so, then after we have added our two sets of days together we then divide by the number of days in a year (365) to get the number of years.

I would give the help file a read on DateDiff, it has some very useful
information for that function.

HTH

Mike Krous
"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
> I am having some problem with a Year Function.
> I have form on which I have 4 field which indicate dates and an
> additional form which sums those dates:
>
> These are the fields:
>
> YEARS
> STARTINGDATE1
> ENDINGDATE1
> STARTINGDATE2
> ENDINGDATE2
>
> In each of DATE fields, I wil be adding a date. The YEARS field
> should sum the values I enter in the other 4 fields.
>
> I have tried simply to add the two other fields to the code below, but > it
> does not work:
>
>

=DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]
)
>
> I have also tried to use two other not visible fields, named A and, A1 > and fill them with your origianal code and then add in the YEARS
> field: =[A]+[A1].
>
> This seems some solution, the only problem is that it may happen that > sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When > this happens, it does not make to calculation. If these fields do show > dates, the calculations is processed.
> Any further help?
>
> Thanks.

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.