Incorporating DATE into formula

S

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
 
T

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
 
B

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)
 
T

Tom Ogilvy

misread the date

dt = DateValue("Dec 31, 2004")

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
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

sylink said:
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
 
S

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top