Incorporating DATE into formula

[sylink]

Having declared "dt" as date constant, how do i incorporate it into
formula involving cell calculation as shown below.

Dim z As Long
Dim dt As Date

dt = 31 / 12 / 2004
z = 2

Do While Range("A" & z) <> ""

If Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value > dt Then

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

ElseIf Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value <= dt Then

Range("R" & z).Select
ActiveCell.FormulaR1C1 = "=(dt-RC[-12])*RC[-7]*(RC[-4]/36600"

Else
Range("R" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop

 

[Tom Ogilvy]

Dim z As Long
Dim dt As Date

dt = DateValue("Dec 13, 2004")
z = 2

Do While Range("A" & z) <> ""

If Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value > dt Then

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

ElseIf Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value <= dt Then

Range("R" & z).Select
ActiveCell.FormulaR1C1 = "=(dt-RC[-12])*RC[-7]*(RC[-4]/36600"

Else
Range("R" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop

 

[Bob Phillips]

Your use of dt is incorrect, 31 /12 /2004 is not a date but va small number,
it is dividing.

Try this

Dim z As Long
Dim dt As Date

dt = DateSerial(2004, 12, 31)
z = 2

Do While Range("A" & z) <> ""

If (Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value > dt) Or _
(Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value <= dt) Then

Range("R" & z).FormulaR1C1 = _
"=(--""" & Format(dt, "yyyy-mm-dd") &
"""-RC[-12])*RC[-7]*(RC[-4]/36600)"

Else
Range("R" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

misread the date

dt = DateValue("Dec 31, 2004")

--
Regards,
Tom Ogilvy

Dim z As Long
Dim dt As Date

dt = DateValue("Dec 13, 2004")
z = 2

Do While Range("A" & z) <> ""

If Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value > dt Then

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

ElseIf Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value <= dt Then

Range("R" & z).Select
ActiveCell.FormulaR1C1 = "=(dt-RC[-12])*RC[-7]*(RC[-4]/36600"

Else
Range("R" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop


--
Regards,
Tom Ogilvy

Having declared "dt" as date constant, how do i incorporate it into
formula involving cell calculation as shown below.

Dim z As Long
Dim dt As Date

dt = 31 / 12 / 2004
z = 2

Do While Range("A" & z) <> ""

If Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value > dt Then

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

ElseIf Range("A" & z).Value <> Range("A" & z + 1).Value And Range("G" &
z).Value <= dt Then

Range("R" & z).Select
ActiveCell.FormulaR1C1 = "=(dt-RC[-12])*RC[-7]*(RC[-4]/36600"

Else
Range("R" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop

 

[sylink]

thanks bob and philip for ur submission. they are quite helpful. pls
can i have any reference or site to read more on date formats
particularly on the format applied by bob.

thanks once more.

chris

 

[Bob Phillips]

Chris,

I don't know of any reference on dates that might be of help to you. The
best place to look is in help on dates, that should give the basic
understanding. Use both the Excel help, and the VBA help.

--

HTH

RP
(remove nothere from the email address if mailing direct)