DATE IN COMPUTATION

[sylink]

I tried without success to compute declaring date as shown below. How
do I declare dates correctly so the formula will compute?

Dim dt, lp1, yb As Date

dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)


Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"

 

[paul.robinson]

Hi
You want
Dim dt as Date, lp1 as Date, yb As Date

Your syntax effectively declares dt and lp1 as variants.
This may still not fix your formulae though!
regards
Paul

 

[Rick Rothstein \(MVP - VB\)]

See inline comments...

I tried without success to compute declaring date as shown below. How
do I declare dates correctly so the formula will compute?

Dim dt, lp1, yb As Date

The above statement is not doing what you think it is. Only yb is being
declared as a Date; both dt and lp1 are being declared as Variants. In
VB/VBA, you must explicitly declare each variable as to its type. So, either
do this...

Dim dt As Date, lp1 As Date, yb As Date

or do it this way...

Dim dt As Date
Dim lp1 As Date
Dim yb As Date

dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"

Can't help you with your actual formula as to whether it does what you want,
but we can help you structure it so that the variables' values are actually
embedded into it. The reason your formula is not working is that you put the
variable names **inside** the quote marks where they are just pieces of text
rather than concatenating the variable names with the surrounding text so
that VBA can see them for the variable that they are. Give this modification
to your statement a try...

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-" & CStr(lp1) & _
")*RC[-7]*RC[-4]/36600)+ ((" & CStr(dt) & _
"-(" & CStr(yb) & " + 1))*RC[-7]*RC[-4]/36600)"

You might be able to get away without encasing the variable names in the
CStr function calls, but I think it is better to explicitly convert the
contained values into text in order to concatenate them as opposed to hoping
VBA with get it right by guessing as to how to coerce your variables'
content.

Rick

 

[sylink]

See inline comments...

I tried without success to compute declaringdateas shown below. How
do I declare dates correctly so the formula will compute?

Dim dt, lp1, yb AsDate

The above statement is not doing what you think it is. Only yb is being
declared as aDate; both dt and lp1 are being declared as Variants. In
VB/VBA, you must explicitly declare each variable as to its type. So, either
do this...

Dim dt AsDate, lp1 AsDate, yb AsDate

or do it this way...

Dim dt AsDate
Dim lp1 AsDate
Dim yb AsDate

dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"

Can't help you with your actual formula as to whether it does what you want,
but we can help you structure it so that the variables' values are actually
embedded into it. The reason your formula is not working is that you put the
variable names **inside** the quote marks where they are just pieces of text
rather than concatenating the variable names with the surrounding text so
that VBA can see them for the variable that they are. Give this modification
to your statement a try...

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-" & CStr(lp1) & _
")*RC[-7]*RC[-4]/36600)+ ((" & CStr(dt) & _
"-(" & CStr(yb) & " + 1))*RC[-7]*RC[-4]/36600)"

You might be able to get away without encasing the variable names in the
CStr function calls, but I think it is better to explicitly convert the
contained values into text in order to concatenate them as opposed to hoping
VBA with get it right by guessing as to how to coerce your variables'
content.

Rick

Thanks Rick for the post. The code ran quite alright but a slight
hitch occurred. It does not produce the correct no. of days within a
given range. For instance,
CStr(dt) & "-RC[-7] gives over 43,000 days instead of 30 days. The
RC[-7] contains the date 01/01/2008

Is there a way to work around this problem? Is the date serial
declaration ok?

 

[Rick Rothstein \(MVP - VB\)]

See inline comments...

I tried without success to compute declaringdateas shown below. How
do I declare dates correctly so the formula will compute?

Dim dt, lp1, yb AsDate

The above statement is not doing what you think it is. Only yb is being
declared as aDate; both dt and lp1 are being declared as Variants. In
VB/VBA, you must explicitly declare each variable as to its type. So,
either
do this...

Dim dt AsDate, lp1 AsDate, yb AsDate

or do it this way...

Dim dt AsDate
Dim lp1 AsDate
Dim yb AsDate

dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"

Can't help you with your actual formula as to whether it does what you
want,
but we can help you structure it so that the variables' values are
actually
embedded into it. The reason your formula is not working is that you put
the
variable names **inside** the quote marks where they are just pieces of
text
rather than concatenating the variable names with the surrounding text so
that VBA can see them for the variable that they are. Give this
modification
to your statement a try...

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-" & CStr(lp1) & _
")*RC[-7]*RC[-4]/36600)+ ((" & CStr(dt) & _
"-(" & CStr(yb) & " +
1))*RC[-7]*RC[-4]/36600)"

You might be able to get away without encasing the variable names in the
CStr function calls, but I think it is better to explicitly convert the
contained values into text in order to concatenate them as opposed to
hoping
VBA with get it right by guessing as to how to coerce your variables'
content.

Rick

Thanks Rick for the post. The code ran quite alright but a slight
hitch occurred. It does not produce the correct no. of days within a
given range. For instance,
CStr(dt) & "-RC[-7] gives over 43,000 days instead of 30 days. The
RC[-7] contains the date 01/01/2008

Is there a way to work around this problem? Is the date serial
declaration ok?

I'm not sure what the problem is as I do not recognize what your formula is
attempting to do nor what it expects its inputted arguments to be. If you
can give us a background on "what is what" with it, perhaps someone might be
able to offer specific advice.

Rick

 

[sylink]

See inline comments...
I tried without success to compute declaringdateas shown below. How
do I declare dates correctly so the formula will compute?
Dim dt, lp1, yb AsDate
The above statement is not doing what you think it is. Only yb is being
declared as aDate; both dt and lp1 are being declared as Variants. In
VB/VBA, you must explicitly declare each variable as to its type. So,
either
do this...
Dim dt AsDate, lp1 AsDate, yb AsDate
or do it this way...
Dim dt AsDate
Dim lp1 AsDate
Dim yb AsDate
dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)
Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"
Can't help you with your actual formula as to whether it does what you
want,
but we can help you structure it so that the variables' values are
actually
embedded into it. The reason your formula is not working is that you put
the
variable names **inside** the quote marks where they are just pieces of
text
rather than concatenating the variable names with the surrounding text so
that VBA can see them for the variable that they are. Give this
modification
to your statement a try...
Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-" & CStr(lp1) & _
")*RC[-7]*RC[-4]/36600)+ ((" & CStr(dt) & _
"-(" & CStr(yb) & " +
1))*RC[-7]*RC[-4]/36600)"
You might be able to get away without encasing the variable names in the
CStr function calls, but I think it is better to explicitly convert the
contained values into text in order to concatenate them as opposed to
hoping
VBA with get it right by guessing as to how to coerce your variables'
content.
Rick

Thanks Rick for the post. The code ran quite alright but a slight
hitch occurred. It does not produce the correct no. of days within a
given range. For instance,
CStr(dt) & "-RC[-7] gives over 43,000 days instead of 30 days. The
RC[-7] contains thedate01/01/2008

Is there a way to work around this problem? Is thedateserial
declaration ok?

I'm not sure what the problem is as I do not recognize what your formula is
attempting to do nor what it expects its inputted arguments to be. If you
can give us a background on "what is what" with it, perhaps someone might be
able to offer specific advice.

Rick

Ok, the formula is designed to compute interest on a given amount for
a given period. So basically the challenge is in obtaining the
duration from two given dates as shown above.

 

[Peter T]

See inline comments...

I tried without success to compute declaringdateas shown below. How
do I declare dates correctly so the formula will compute?

Dim dt, lp1, yb AsDate

The above statement is not doing what you think it is. Only yb is being
declared as aDate; both dt and lp1 are being declared as Variants. In
VB/VBA, you must explicitly declare each variable as to its type. So,
either
do this...

Dim dt AsDate, lp1 AsDate, yb AsDate

or do it this way...

Dim dt AsDate
Dim lp1 AsDate
Dim yb AsDate

dt = DateSerial(2008, 1, 31)
lp1 = DateSerial(2007, 12, 31)
yb = DateSerial(2008, 1, 1)

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-lp1)*RC[-7]*RC[-4]/
36600)+ ((dt-(yb + 1))*RC[-7]*RC[-4]/36600)"

Can't help you with your actual formula as to whether it does what you
want,
but we can help you structure it so that the variables' values are
actually
embedded into it. The reason your formula is not working is that you put
the
variable names **inside** the quote marks where they are just pieces of
text
rather than concatenating the variable names with the surrounding text so
that VBA can see them for the variable that they are. Give this
modification
to your statement a try...

Range("R" & z).FormulaR1C1 = "=(((RC[-12]+1)-" & CStr(lp1) & _
")*RC[-7]*RC[-4]/36600)+ ((" & CStr(dt) & _
"-(" & CStr(yb) & " +
1))*RC[-7]*RC[-4]/36600)"

You might be able to get away without encasing the variable names in the
CStr function calls, but I think it is better to explicitly convert the
contained values into text in order to concatenate them as opposed to
hoping
VBA with get it right by guessing as to how to coerce your variables'
content.

Rick

Thanks Rick for the post. The code ran quite alright but a slight
hitch occurred. It does not produce the correct no. of days within a
given range. For instance,
CStr(dt) & "-RC[-7] gives over 43,000 days instead of 30 days. The
RC[-7] contains thedate01/01/2008

Is there a way to work around this problem? Is thedateserial
declaration ok?

I'm not sure what the problem is as I do not recognize what your formula is
attempting to do nor what it expects its inputted arguments to be. If you
can give us a background on "what is what" with it, perhaps someone might be
able to offer specific advice.

Rick

Ok, the formula is designed to compute interest on a given amount for
a given period. So basically the challenge is in obtaining the
duration from two given dates as shown above.

I'd say the challenge for other readers is to understand what you want!

Why not manually enter the correct formula. Ensure the cell is selected, in
the Immediate window type
?activecell.formular1c1
hit enter and post the result. Also include the address of the cell together
with the values of any other referenced cells.

In the meantime, some ideas for including dates in string formulas

Sub test()
Dim sF As String
Dim dt As Date

dt = Date
sF = "=DATE(" & Format(dt, "YYYY, m, d") & ") + RC[-1]"
'' or if date can be hard coded
' sF = "=DATE(2008, 2, 14) + RC[-1]"

Range("A1") = 7
Range("B1").FormulaR1C1 = sF

dt = Date
sF = "=" & CLng(dt) + 7 & "-RC[-1]"
Range("A2") = Date

Range("B2").FormulaR1C1 = sF
Range("B2").ClearFormats
End Sub

Regards,
Peter T