|
date problem
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 |
date problem
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 |
date problem
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 |
date problem
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 |
date problem
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 |
date problem
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 |
date problem
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 |
| All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com