I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras

it has no clue what 2 letter month designators are. i see 17Ju04. is this
June or July? i can figure the other ones, but excel can't. can't you get at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras

Hi Gary
No I can't but june=jn,july=jy,march=mr,may=my
I hope that helps and thank you so far

regards Bill
--
bill gras


"Gary Keramidas" wrote:

it has no clue what 2 letter month designators are. i see 17Ju04. is this
June or July? i can figure the other ones, but excel can't. can't you get at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras

here's something very rudimentary, but it should do what you want. give it a
try, i don't know the exact format of the data in the column, but it worked
here. change the range in the for each line and if any 2 character month
designators are different, change those in the 2 places i have coded.


Sub replace_Text()
Dim cell As Range
On Error Resume Next
For Each cell In Range("A1:A40")

p = Application.Evaluate("=mid(" & cell.Value & ",len(" & cell.Value &
")-3,2)")
If p = "Ja" Then
cell.Replace _
what:="Ja", replacement:="Jan"
End If

If p = "Fe" Then
cell.Replace _
what:="Fe", replacement:="Feb"
End If

If p = "Mr" Then
cell.Replace _
what:="Mr", replacement:="Mar"
End If

If p = "Ap" Then
cell.Replace _
what:="Ap", replacement:="Apr"
End If

If p = "My" Then
cell.Replace _
what:="My", replacement:="May"
End If

If p = "Jn" Then
cell.Replace _
what:="Jn", replacement:="Jun"
End If

If p = "Jl" Then
cell.Replace _
what:="Jl", replacement:="Jul"
End If

If p = "Au" Then
cell.Replace _
what:="Au", replacement:="Aug"
End If

If p = "Se" Then
cell.Replace _
what:="Se", replacement:="Sep"
End If

If p = "Oc" Then
cell.Replace _
what:="Oc", replacement:="Oct"
End If

If p = "No" Then
cell.Replace _
what:="No", replacement:="Nov"
End If

If p = "De" Then
cell.Replace _
what:="De", replacement:="Dec"
End If
If p = "" Then
End If

Debug.Print p

Next
End Sub


--


Gary


"bill gras" wrote in message
...
Hi Gary
No I can't but june=jn,july=jy,march=mr,may=my
I hope that helps and thank you so far

regards Bill
--
bill gras


"Gary Keramidas" wrote:

it has no clue what 2 letter month designators are. i see 17Ju04. is this
June or July? i can figure the other ones, but excel can't. can't you get
at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no
luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras

Hi Gary
Thank you very much , it works great , what would we do without people
like you
Once again thanks
regards bill
--
bill gras


"Gary Keramidas" wrote:

here's something very rudimentary, but it should do what you want. give it a
try, i don't know the exact format of the data in the column, but it worked
here. change the range in the for each line and if any 2 character month
designators are different, change those in the 2 places i have coded.


Sub replace_Text()
Dim cell As Range
On Error Resume Next
For Each cell In Range("A1:A40")

p = Application.Evaluate("=mid(" & cell.Value & ",len(" & cell.Value &
")-3,2)")
If p = "Ja" Then
cell.Replace _
what:="Ja", replacement:="Jan"
End If

If p = "Fe" Then
cell.Replace _
what:="Fe", replacement:="Feb"
End If

If p = "Mr" Then
cell.Replace _
what:="Mr", replacement:="Mar"
End If

If p = "Ap" Then
cell.Replace _
what:="Ap", replacement:="Apr"
End If

If p = "My" Then
cell.Replace _
what:="My", replacement:="May"
End If

If p = "Jn" Then
cell.Replace _
what:="Jn", replacement:="Jun"
End If

If p = "Jl" Then
cell.Replace _
what:="Jl", replacement:="Jul"
End If

If p = "Au" Then
cell.Replace _
what:="Au", replacement:="Aug"
End If

If p = "Se" Then
cell.Replace _
what:="Se", replacement:="Sep"
End If

If p = "Oc" Then
cell.Replace _
what:="Oc", replacement:="Oct"
End If

If p = "No" Then
cell.Replace _
what:="No", replacement:="Nov"
End If

If p = "De" Then
cell.Replace _
what:="De", replacement:="Dec"
End If
If p = "" Then
End If

Debug.Print p

Next
End Sub


--


Gary


"bill gras" wrote in message
...
Hi Gary
No I can't but june=jn,july=jy,march=mr,may=my
I hope that helps and thank you so far

regards Bill
--
bill gras


"Gary Keramidas" wrote:

it has no clue what 2 letter month designators are. i see 17Ju04. is this
June or July? i can figure the other ones, but excel can't. can't you get
at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no
luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras

you're welcome.
the experts help me, so i try to help if i can. i'm sure one of them could
write some more compact code.
i'm glad it suited your needs
--


Gary


"bill gras" wrote in message
...
Hi Gary
Thank you very much , it works great , what would we do without people
like you
Once again thanks
regards bill
--
bill gras


"Gary Keramidas" wrote:

here's something very rudimentary, but it should do what you want. give
it a
try, i don't know the exact format of the data in the column, but it
worked
here. change the range in the for each line and if any 2 character month
designators are different, change those in the 2 places i have coded.


Sub replace_Text()
Dim cell As Range
On Error Resume Next
For Each cell In Range("A1:A40")

p = Application.Evaluate("=mid(" & cell.Value & ",len(" & cell.Value &
")-3,2)")
If p = "Ja" Then
cell.Replace _
what:="Ja", replacement:="Jan"
End If

If p = "Fe" Then
cell.Replace _
what:="Fe", replacement:="Feb"
End If

If p = "Mr" Then
cell.Replace _
what:="Mr", replacement:="Mar"
End If

If p = "Ap" Then
cell.Replace _
what:="Ap", replacement:="Apr"
End If

If p = "My" Then
cell.Replace _
what:="My", replacement:="May"
End If

If p = "Jn" Then
cell.Replace _
what:="Jn", replacement:="Jun"
End If

If p = "Jl" Then
cell.Replace _
what:="Jl", replacement:="Jul"
End If

If p = "Au" Then
cell.Replace _
what:="Au", replacement:="Aug"
End If

If p = "Se" Then
cell.Replace _
what:="Se", replacement:="Sep"
End If

If p = "Oc" Then
cell.Replace _
what:="Oc", replacement:="Oct"
End If

If p = "No" Then
cell.Replace _
what:="No", replacement:="Nov"
End If

If p = "De" Then
cell.Replace _
what:="De", replacement:="Dec"
End If
If p = "" Then
End If

Debug.Print p

Next
End Sub


--


Gary


"bill gras" wrote in message
...
Hi Gary
No I can't but june=jn,july=jy,march=mr,may=my
I hope that helps and thank you so far

regards Bill
--
bill gras


"Gary Keramidas" wrote:

it has no clue what 2 letter month designators are. i see 17Ju04. is
this
June or July? i can figure the other ones, but excel can't. can't you
get
at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but
no
luck
I need to have the difference in days between two dates. All I get
is
#VALUE!

can some one please help

bill gras

--
bill gras

Here is a more compact version of the above code. It should work, so long as
you make sure there is no extra whitespace in the date cells.

Option Explicit

Sub ReplaceDates()

' Change this range to wherever your data actually is
With Range("A1:A10")
' Change any of these replacement codes as necessary
.Replace what:="Ja", replacement:="Jan"
.Replace what:="Fe", replacement:="Feb"
.Replace what:="Mr", replacement:="Mar"
.Replace what:="Ap", replacement:="Apr"
.Replace what:="My", replacement:="May"
.Replace what:="Jn", replacement:="Jun"
.Replace what:="Jl", replacement:="Jul"
.Replace what:="Au", replacement:="Aug"
.Replace what:="Se", replacement:="Sep"
.Replace what:="Oc", replacement:="Oct"
.Replace what:="No", replacement:="Nov"
.Replace what:="De", replacement:="Dec"
End With
End Sub

"bill gras" wrote:

Hi Gary
Thank you very much , it works great , what would we do without people
like you
Once again thanks
regards bill
--
bill gras


"Gary Keramidas" wrote:

here's something very rudimentary, but it should do what you want. give it a
try, i don't know the exact format of the data in the column, but it worked
here. change the range in the for each line and if any 2 character month
designators are different, change those in the 2 places i have coded.


Sub replace_Text()
Dim cell As Range
On Error Resume Next
For Each cell In Range("A1:A40")

p = Application.Evaluate("=mid(" & cell.Value & ",len(" & cell.Value &
")-3,2)")
If p = "Ja" Then
cell.Replace _
what:="Ja", replacement:="Jan"
End If

If p = "Fe" Then
cell.Replace _
what:="Fe", replacement:="Feb"
End If

If p = "Mr" Then
cell.Replace _
what:="Mr", replacement:="Mar"
End If

If p = "Ap" Then
cell.Replace _
what:="Ap", replacement:="Apr"
End If

If p = "My" Then
cell.Replace _
what:="My", replacement:="May"
End If

If p = "Jn" Then
cell.Replace _
what:="Jn", replacement:="Jun"
End If

If p = "Jl" Then
cell.Replace _
what:="Jl", replacement:="Jul"
End If

If p = "Au" Then
cell.Replace _
what:="Au", replacement:="Aug"
End If

If p = "Se" Then
cell.Replace _
what:="Se", replacement:="Sep"
End If

If p = "Oc" Then
cell.Replace _
what:="Oc", replacement:="Oct"
End If

If p = "No" Then
cell.Replace _
what:="No", replacement:="Nov"
End If

If p = "De" Then
cell.Replace _
what:="De", replacement:="Dec"
End If
If p = "" Then
End If

Debug.Print p

Next
End Sub


--


Gary


"bill gras" wrote in message
...
Hi Gary
No I can't but june=jn,july=jy,march=mr,may=my
I hope that helps and thank you so far

regards Bill
--
bill gras


"Gary Keramidas" wrote:

it has no clue what 2 letter month designators are. i see 17Ju04. is this
June or July? i can figure the other ones, but excel can't. can't you get
at
least 3 characters?


--


Gary


"bill gras" wrote in message
...
I'm stuck with a date problem I can not fix
I import a file that has s date column as follows:

18Oc05
27Se05
22Se05
13De04
6No04
7Oc04
17Ju04
and so on down to 1200 rows, I tried all sorts of cell formats but no
luck
I need to have the difference in days between two dates. All I get is
#VALUE!

can some one please help

bill gras

--
bill gras