Bug in a built-in VBA date function?

[suomi]

Hi all,

Is this a bug or my misunderstanding with date VBA functions: DateDiff
and DateAdd:
The DateDiff function returns always an integer, which I suppose mean,
that between 2 dates there is at least full period of difference. In
other words, DateDiff function does not round, but cuts off the decimal
places in the value of difference. Now look at example:

ActiveCell.Value = DateDiff("q", Date, "2007-04-06")
ActiveCell.Offset(1, 0).Value = DateAdd("q", 1, Date)

Date is a today's date, when I performed it, it was the 9th of January.
As a result I got:

1,0000
2007-04-09

From the first row, I suppose, that between 2007-01-09 and 2007-04-06

there is at least one quarter, or one quarter with decimal places
after. I checked, and the boundary date is here 2007-04-01, which means
that I shall be sure there's one quarter and a bit more of difference.
So I supposed, that adding one (1, without decimal places) quarter to
2007-01-09 would return 2007-04-01. But it did not. It returned
2007-04-09. Why?

I need these functions for this kind of task: I have a date and I need
to find an approriate date before and after this one given, so, that
the found dates are the beginning of two adjacent "full" periods, like
quarters.

Any advice on this issue?

 

[Jim Rech]

Datediff (with 'q' at least) does not seem to be suited to your needs. This
returns 1:

DateDiff("q", "2007-03-31", "2007-04-01")

It seems to find the quarter number each date is in and return that
difference.

--
Jim
| Hi all,
|
| Is this a bug or my misunderstanding with date VBA functions: DateDiff
| and DateAdd:
| The DateDiff function returns always an integer, which I suppose mean,
| that between 2 dates there is at least full period of difference. In
| other words, DateDiff function does not round, but cuts off the decimal
| places in the value of difference. Now look at example:
|
| ActiveCell.Value = DateDiff("q", Date, "2007-04-06")
| ActiveCell.Offset(1, 0).Value = DateAdd("q", 1, Date)
|
| Date is a today's date, when I performed it, it was the 9th of January.
| As a result I got:
|
| 1,0000
| 2007-04-09
|
| >From the first row, I suppose, that between 2007-01-09 and 2007-04-06
| there is at least one quarter, or one quarter with decimal places
| after. I checked, and the boundary date is here 2007-04-01, which means
| that I shall be sure there's one quarter and a bit more of difference.
| So I supposed, that adding one (1, without decimal places) quarter to
| 2007-01-09 would return 2007-04-01. But it did not. It returned
| 2007-04-09. Why?
|
| I need these functions for this kind of task: I have a date and I need
| to find an approriate date before and after this one given, so, that
| the found dates are the beginning of two adjacent "full" periods, like
| quarters.
|
| Any advice on this issue?
|