|
Incorporating DATE into formula
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 |
Incorporating DATE into formula
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" wrote in message oups.com... 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 |
Incorporating DATE into formula
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) "sylink" wrote in message oups.com... 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 |
Incorporating DATE into formula
misread the date
dt = DateValue("Dec 31, 2004") -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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" wrote in message oups.com... 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 |
Incorporating DATE into formula
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 |
Incorporating DATE into formula
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) "sylink" wrote in message oups.com... 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 |
| All times are GMT +1. The time now is 10:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com