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


BRC wrote:
>
> 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

--

Dave Peterson

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 & """))"

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"BRC" wrote:

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