Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in a built-in VBA date function?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in a built-in VBA date function?
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 "suomi" wrote in message oups.com... | 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? | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there any built in function to draw lines | Excel Worksheet Functions | |||
Now not regonized as a built in Function, in some spreadsheets? | Excel Programming | |||
Use An Excel Built-In Function Entirely Within VBA | Excel Programming | |||
Access Recordset with Built-In Function | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |