Solved: string split for date

[CatDaddy]

i have a collumn of data that has dates in the format:

02-OCT-11/01-NOV-11

i am trying to pull the first half for comparison but it isnt working

For Each cell In Range("T2:T" & Range("A65000").End(xlUp).Row)

r = cell.Row
Dim str As Variant
Dim date1 As Date
str = Split(cell.Value, "/")
date1 = CDate(str(0))

Range("BE" & r).Value = DateDiff("d", date1, Now)

any ideas?

[mancubus]

hi.

try removing "CDate" since splitted text returns dates...

[GTO]

It appears to work for me. What is it doing for you?

[mdmackillop]

I suspect an error in your range selection
try

For Each Cell In Range("T2:T" & Cells(Rows.Count, "T").End(xlUp).Row)

[mancubus]

It appears to work for me. What is it doing for you?

hi GTO.

it worked for me after removing Cdate.
have an idea?
maybe because of regional settings?

[Aflatoon]

I would also advise against using str as a variable name since it is a VBA function.

[CatDaddy]

thank you for all the advice...its definitely not the range selection because it worked for the first run, i am trying to widen the scope of what my macro can do...i will try again removing the cdate and changing the variable name

[CatDaddy]

ugh...the problem was about halfway through the spreadsheet (65000 lines) there is a patch of dates that just say NA...stupid reporting problem!!!!!!!!! anyhow thanks for the help and im fairly confident I'm up and running at 100% now.

thanks again

[GTO]

Glad you found the issue and thanks for letting us know :-)

Mark