Incorporating DATE into formula

  • Thread starter Thread starter sylink
  • Start date Start date
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
 
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
 
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)
 
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
Click to expand...
Click to expand...
 
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
 
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)
 
Back
Top