can anyone tell me whats wrong with this line of code?

  • Thread starter Thread starter BRC
  • Start date Start date
B

BRC

dim enddt
enddt = Range("c16").value
Range("d16").formula = "=if(" & endDt & " > " & Date & " , " & Date &
" , " & endDt & ")"

What I am getting is in cell d16 is something like
"=IF(12/15/1999 > 1/5/2007, 1/5/2007, 12/15/1999)"
which is translating (displayed) as 1/0/1900
what i was trying to get is 12/15/1999
Cell d16 is formated a date m/d/yyyy
Thanks for any advice. BRC
 
I would think you'd want the formula to look more like:

=if(c16>somedate,somedate,c16)

If I were writing it in the cell, I'd use:

=if(c16>today(),today(),c16)

or if I wanted a date that didn't change:

=if(c16>date(2007,1,5),date(2007,1,5),c16)

So my code would look like:

Range("d16").Formula = "=if(c16>today(),today(),c16)"

'or if I wanted a date that didn't change:

Range("d16").Formula _
= "=if(c16>date(" & Year(Date) & "," & Month(Date) & "," & Day(Date) _
& "),date(" & Year(Date) & "," & Month(Date) & "," & Day(Date) _
& "),c16)"
 
You need to use datevalue to get the date recognised.

enddt = Range("c16").Value
Range("d16").Formula = _
"=if(datevalue(""" & enddt & _
""") > datevalue(""" & Date & """)," & _
" datevalue(""" & Date & _
"""),datevalue(""" & enddt & """))"
 

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

Similar Threads

Excel Formula Dates 5
whats wrong with this code 2
DateRange Lookup no exact match, can't use lesser 4
What’s wrong with this countif procedure ? 2
Datavalidation with shrinking non blank values 1
Whats wrong with this code 5
Loop, Compare, Match & Total 1
Date Format returned from Userform to Excel worksheet 7

Back
Top