Problem with working out the amount of quarters between two dates
 

I am trying to work out the remaining quarters between two dates. I
can get a result which I then want to floor. However excel is giving
me a Error '1004' with the following text: Unable to get the Floor
property of the WorksheetFunction class.
This doesn't surprise me as Floor is a method within that class. I
post my code below so as to show what I am trying to achieve.

installQ = Application.WorksheetFunction.Floor((((DateValue(t bInstall1.Value)
- DateValue(tbTransDate.Value)) / 365) * 4), 0)

Best Regards
Nigel Brown

Problem with working out the amount of quarters between two dates
 

Nigel,

have a look at VBA DateDiff function.
dont confuse with undocumented worksheetfunction DateDif


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Nigel Brown) wrote:

I am trying to work out the remaining quarters between two dates. I
can get a result which I then want to floor. However excel is giving
me a Error '1004' with the following text: Unable to get the Floor
property of the WorksheetFunction class.
This doesn't surprise me as Floor is a method within that class. I
post my code below so as to show what I am trying to achieve.

installQ =
Application.WorksheetFunction.Floor((((DateValue(t bInstall1.Value) -
DateValue(tbTransDate.Value)) / 365) * 4), 0)

Best Regards
Nigel Brown

Problem with working out the amount of quarters between two dates
 

dtEnd = DateValue("01/01/2004")
dtStart = DateValue("06/25/2002")
? Application.WorksheetFunction.Floor((((dtEnd-dtStart) / 365) * 4), 1)
6

Works fine. I suspect using a zero as the last argument is the source of
your problem. when you use Worksheetfunction.Floor and it returns an error,
it raises a trappable error like 1004. With the zero on the end, it raises
an error as it does in the worksheet itself.

Regards,
Tom Ogilvy


"Nigel Brown" wrote in message
om...
I am trying to work out the remaining quarters between two dates. I
can get a result which I then want to floor. However excel is giving
me a Error '1004' with the following text: Unable to get the Floor
property of the WorksheetFunction class.
This doesn't surprise me as Floor is a method within that class. I
post my code below so as to show what I am trying to achieve.

installQ =
Application.WorksheetFunction.Floor((((DateValue(t bInstall1.Value)
- DateValue(tbTransDate.Value)) / 365) * 4), 0)

Best Regards
Nigel Brown