Using DateValue function

[Ralph Elmerick]

I am trying to find the number of days between two date
using the following formula: =IF(D2=(DATEVALUE
("1986/1/1")),(DATEVALUE("2004/12/15")-I2),(DATEVALUE
("2004/12/15")-D2)) in a macro. Here is the code in the
macro:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DATEVALUE
(""2005/01/01"")-RC[3]),(DATEVALUE(""2005/01/01"")-RC[-
2]))"
I need to have the 2nd and 3rd dates be dynamic when I
run the script and not have them hardcoded. The 1986/1/1
date will always be the same date. I have tried the
following code:
vardate = Right(Date, 4) & "/" & Month(Date) & "/1"
DateValueUS = Application.Evaluate("DateValue(""" &
vardate & """)")
' ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DateValue
(DATEVALUEUS)-RC[3]),(DATEVALUEUS-RC[-2]))"
but it does not work. Does anybody have a solution to
populate the column with the number of days between two
dates? Thanks.

 

[Sharad Naik]

Try:-
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DateValue(" & DATEVALUEUS &
")-RC[3]),(DATEVALUE(" & DATEVALUEUS & ")-RC[-2]))"

Sharad

 

[Guest]

Thanks Sharad. I tried that and now I get
DateValueUS = 38353.
=IF(D2=(DATEVALUE("1986/1/1")),(DATEVALUE(38353)-I2),
(DATEVALUE(38353)-D2))
Which is a step close in that now I indeed get the value
for the DateValue, but it does not make the calculations
and substitute in the subtraction of the two dates.

-----Original Message-----
Try:-
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DateValue (" & DATEVALUEUS &
")-RC[3]),(DATEVALUE(" & DATEVALUEUS & ")-RC[-2]))"

Sharad

I am trying to find the number of days between two date
using the following formula: =IF(D2=(DATEVALUE
("1986/1/1")),(DATEVALUE("2004/12/15")-I2),(DATEVALUE
("2004/12/15")-D2)) in a macro. Here is the code in the
macro:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DATEVALUE
(""2005/01/01"")-RC[3]),(DATEVALUE(""2005/01/01"")-RC[-
2]))"
I need to have the 2nd and 3rd dates be dynamic when I
run the script and not have them hardcoded. The 1986/1/1
date will always be the same date. I have tried the
following code:
vardate = Right(Date, 4) & "/" & Month(Date) & "/1"
DateValueUS = Application.Evaluate("DateValue(""" &
vardate & """)")
' ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(DateValue
(DATEVALUEUS)-RC[3]),(DATEVALUEUS-RC[-2]))"
but it does not work. Does anybody have a solution to
populate the column with the number of days between two
dates? Thanks.

.

 

[Ralph Elmerick]

Sharad Thanks.
I tried your suggestion and now I get
=IF(D2=(DATEVALUE("1986/1/1")),(DATEVALUE(38353)-I2),(DATEVALUE(38353)-D
2))
in the field with #VALUE in the field. The DateValueUS is indeed 38353
so we have made progress it just does not provide the value I need for
the number of days. It is not making the subtraction between the dates.

Thanks

 

[Sharad Naik]

Well Ralph I made a mistake makeing it to work as DateValue(DateValueUS).
Since DaveValueUS is already date value, again DateValue() is causing the
error.

SO try below:

ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]=(DATEVALUE(""1986/1/1"")),(" & DATEVALUEUS &
"-RC[3]),(" & DATEVALUEUS & "-RC[-2]))"
Sharad

 

[Ralph Elmerick]

Thank you so much that does the trick. It works perfectly. This list
is the greatest.

Ralph Elmerick