Solved: Macro to change text to date in a set range of cells.

[Kranky]

Hello again.

I'm having issues converting text to dates. The text is imported from another program via excel. That process creates data that could be date as text or the date in the wrong format. there may also be a lot of empty cells.

So what I need is to be able to run a macro that takes the text or wrongly formated date value in a range of cells and convert it to a date value with the format "dd-mmm".

I have the code below:
[vba]Sub ChangedateSF()
For Each Cell In Sheets("SF").Range("B32:B171")
If IsDate(Cell.Value) Then
LValue = Cell.Value
Cell.Value = Format(DateValue(Cell.Value), "dd-mmm")
End If
Next Cell
End Sub[/vba]

this works ok but is VERY slow and I have to run 3 different macros, one for each sheet (RF,SF and SSF) as I dont know how to combine it all into one script.

Any help would be greatly appreciated

[Bob Phillips]

[VBA]Sub ChangedateSF()

With Worksheets("SF").Range("B32:B171")

.TextToColumns Destination:=Range("B32"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
FieldInfo:=Array(1, 1)
.NumberFormat = "dd-mmm"
End With
End Sub[/VBA]

[Kranky]

Thankyou - I'll give that a run today.

[Kranky]

Thankyou xld

I've been running my code all the while and its going well till I got to a sheet it wouldn't work on. Swapped it out for your code and it sorted it straight up.

Cheers
Kranky

[BrianMH]

I have this sort of issue quite a bit, along with some other formatting issues after importing. I created a script you might be interested in.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1118

[Kranky]

Thanks Brian I'll give that one a run as well. xld's code didnt go as well as I thought by still taking dates formated in the American style and converting them to all January dates.

[Kranky]

Ok so none of these are working now???

Dates pasted as text in the american style stay in the american style and not being converted.

Trimmer didnt work - got stuck with an undefined variable at Ln68
[VBA] For Each a In arr
[/VBA]

Any pointers in the right direction would be appreciated.
Regards
Kranky

[snb]

Please post a sample sheet, so we can check code using the real data.

[VBA]
Sub M_snb()
[D32171].NumberFormat = "dd-mmm"
[D32171] = [if(B32:B171="","",abs(B32:B171))]
End Sub
[/VBA]

[Bob Phillips]

So what happened between post #4 when it was all sorted and post #6? Are you saying it worked fine on normal dates, but then you got a book with those American format dates? If so, you could try

[VBA]Sub ChangedateSF()

With Worksheets("SF").Range("B32:B171")

.TextToColumns Destination:=Range("B32"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
FieldInfo:=Array(1, 3)
.NumberFormat = "dd-mmm"
End With
End Sub[/VBA]

although knowing when to run which might be tricky.

[Kranky]

So what happened between post #4 when it was all sorted and post #6? Are you saying it worked fine on normal dates, but then you got a book with those American format dates? .

Humble appologies - it didnt work - I hadn't looked all the way down the coloumn.

If so, you could try

[vba]Sub ChangedateSF()

With Worksheets("SF").Range("B32:B171")

.TextToColumns Destination:=Range("B32"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
FieldInfo:=Array(1, 3)
.NumberFormat = "dd-mmm"
End With
End Sub[/vba]

although knowing when to run which might be tricky.

Having to have someone else make a decision is not an option unfortunately.

I have attached a sample file (I think), with macros in situ.

[Kranky]

Please post a sample sheet, so we can check code using the real data.

Have done

[vba]
Sub M_snb()
[D32171].NumberFormat = "dd-mmm"
[D32171] = [if(B32:B171="","",abs(B32:B171))]
End Sub
[/vba]

thankyou but like all the other bits of code it is converting American formated text as dates or dates to "dd-mmm" however 11/01/2012 becomes 11-Jan when I want it to be 01-Nov. Hope that makes things a bit clearer.

I'm a bit perplexed why the original code doesn't work on a new workbook when it worked fine on the other workbook (each month has its own workbook).

[Bob Phillips]

If you know they are American dates, you have a real problem because it sees 11/01 as 11th Jan full stop, no question, outside of the USA. If you know that they are always American dates, then you will have to process them row by row extracting the date parts.

[Kranky]

I'm a bit perplexed why the original code doesn't work on a new workbook when it worked fine on the other workbook (each month has its own workbook).

After mucking around for ages got sick of it and went to something else (which just happened to be the previous months workbook). Ended up trying all the codes on that sheet and went well with all of them. Opened the Nov sheet and run my code again (without changing it) and away it went - perfect. Except it wasn't perfect this morning and I'm blown if I know why it does that. Yes I had macro's enabled

I just ran the macro ChangedatevbaxSF on your example workbook and it worked fine.

I just tried it on the example workbook and no go per the attached. Different default settings?

thanks for having a crack xld

Regards
Kranky

[snb]

I used:

[VBA]
Sub M_snb()
[SSF!F2:F141].NumberFormat = "dd-mmm"
[SSF!F2:F141] = [if(ssf!B2:B141="","",text(abs(SSF!B2:B141),"yyyy-mm-dd"))]
End Sub
[/VBA]
1 nov returns as 1 nov

[Kranky]

I used:

[vba]
Sub M_snb()
[SSF!F2:F141].NumberFormat = "dd-mmm"
[SSF!F2:F141] = [if(ssf!B2:B141="","",text(abs(SSF!B2:B141),"yyyy-mm-dd"))]
End Sub
[/vba]
1 nov returns as 1 nov

It returned 11-Jan for me on the test sheet.

[Bob Phillips]

The attached is already all good dates?

[Kranky]

The attached is already all good dates?

No, halfway down the dates change. Where they say 11-Jan, 11-Feb etc should be 01-Nov, 02-Nov etc. thats the big issue for me.

[Bob Phillips]

I din't say they were what you wanted, I said they were already dates. That is they have been converted already, so there is no hope of converting them now - too late.

I have really become confused throughout this thread now. I am not sure whether you are receiving string dates that are supposed to be US format, non-US format or both. Sometimes you seem to be saying it works, then you say it doesn't, but I am not sure what is not working, what circumstances it is not working.

[Kranky]

I have really become confused throughout this thread now..

you and me both.

I am not sure whether you are receiving string dates that are supposed to be US format, non-US format or both. .

This is the root of the problem. I am recieving a column of strings that will have some as dates in dd/mm/yyyy, some as dates in text format that may be dd/mm/yyyy or mm/dd/yyyy. This was why I used DateValue in the original code so it looked at the underlying value and not the actual value and then formated it to dd-mmm.

Sometimes you seem to be saying it works, then you say it doesn't, but I am not sure what is not working, what circumstances it is not working.

My appologies. Both yours and mine worked initially then I copied the file to a new one and they didnt work until I closed the file and opened it again. Yep frustrating to say the least and I appologise for the confusion.

My code is working so I'm running with it and I think we'll put this thread to bed. Thankyou for your time, its appreciated.

Its a long journey for me. This spreadsheet has been a beast because I was given it to finish and its been ongoing for 3 months. Instead of looking at the underlying structure i run with it rather than redoing it from scrath which wouldn't have been much of an issue at the time. I regret this and its a lesson learned. The other lesson is that I don't have a good enough understanding of VB to properly explain my issues, this thread is proof of that. I'll be better at that next time.

Thankyou everyone who contributed.

Regards
Kranky