Oct 29 2020 10:11 AM
Hi - I'm constantly pulling data out of a membership database and I need dates for some of my transactions. The problem is that when I pull the data out of the database all my dates come out in text format. When I try to select the column and change the number format to date instead of text, I then have to go in and activate each cell, click in each cell and hit enter to convert the text to date. It's a lot of data, so this isn't an ideal solution. Is there a shortcut around this? What am I missing?
Thanks! Eileen
Oct 29 2020 10:27 AM
Fix text-formatted numbers by applying a number format
With your permission, if I can recommend you, add a MS Excel file (without sensitive data) to your project.
Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.
* Knowing the Excel version and operating system would also be an advantage.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Oct 29 2020 10:38 AM - edited Oct 29 2020 10:42 AM
Yeah, I know how to change the number formatting, it's just that I always have problems with the text to date conversion. Cells do not convert to dates automatically. I have to edit each cell individually for the format change to activate.
I need to change these text "dates" to actual dates so that I can sort by date and not A to Z. Does that make sense? I have version 2009 - OS Windows 10 Pro
Oct 29 2020 11:11 AM
Solution
Sub bla()
Dim a as long, i As long
With Sheets("Excell problem")
.Columns(1).NumberFormat = "DDD.DD.MMMM.YYYY"
a = .Cells(Cells.Rows.Count, 1).End(xlUp).Row
For i = a To 4 Step -1
.Cells(i, 1).Value = CDate(.Cells(i, 1).Value)
Next
End With
Next
End Sub
Hope I was able to help you.
Thank you for your understanding and patience
And…Please keep asking here - I just taught myself Excel with the help of this forum… nearly :)
Nikolino
I know I don't know anything (Socrates)
Oct 29 2020 11:17 AM
Oct 29 2020 11:11 AM
Solution
Sub bla()
Dim a as long, i As long
With Sheets("Excell problem")
.Columns(1).NumberFormat = "DDD.DD.MMMM.YYYY"
a = .Cells(Cells.Rows.Count, 1).End(xlUp).Row
For i = a To 4 Step -1
.Cells(i, 1).Value = CDate(.Cells(i, 1).Value)
Next
End With
Next
End Sub
Hope I was able to help you.
Thank you for your understanding and patience
And…Please keep asking here - I just taught myself Excel with the help of this forum… nearly :)
Nikolino
I know I don't know anything (Socrates)