VBA to Fix Dates
 

I have to pull data from various databases, who have some crazy date formats.
In most cases, I have to drop the data into Excel anyway, so I created a
macro to "fix" all the dates to all behave the same, and appear the same:

Sub DateRemoveZeros()
Selection.Replace What:="01/", Replacement:="1/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="02/", Replacement:="2/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="03/", Replacement:="3/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="04/", Replacement:="4/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="05/", Replacement:="5/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="06/", Replacement:="6/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="07/", Replacement:="7/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="08/", Replacement:="8/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="09/", Replacement:="9/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "m/d/yyyy"
End Sub

But, suddenly, the bugger doesn't appear to be working. I have uploaded an
example to play with:
http://write-me.org/screenshots/DateProblem.xls

Please help me enhance the VBA script. Thank you.

--
Message posted via http://www.officekb.com

VBA to Fix Dates
 

On Apr 23, 2:47 pm, "undrline" <u28594@uwe wrote:
I have to pull data from various databases, who have some crazy date formats.
In most cases, I have to drop the data into Excel anyway, so I created a
macro to "fix" all the dates to all behave the same, and appear the same:

Sub DateRemoveZeros()
Selection.Replace What:="01/", Replacement:="1/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="02/", Replacement:="2/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="03/", Replacement:="3/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="04/", Replacement:="4/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="05/", Replacement:="5/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="06/", Replacement:="6/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="07/", Replacement:="7/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="08/", Replacement:="8/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="09/", Replacement:="9/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "m/d/yyyy"
End Sub

But, suddenly, the bugger doesn't appear to be working. I have uploaded an
example to play with:http://write-me.org/screenshots/DateProblem.xls

Please help me enhance the VBA script. Thank you.

--
Message posted viahttp://www.officekb.com

I took a look at the spreadsheet, and the dates seem to look fine to
me. However, if you want some additional ways to do your
"replacement," consider searching the Date & Time category in the
function box (Menu Bar: Insert/Function) in order to use the existing
data with preset Excel functions. I'd also look up the right, left,
mid, len, search, and substitute functions. You could also create a
For...Next loop and "pick" out the day, month, and year data and put
it together in a date format. You could also do Text to columns (Menu
Bar: Data/Text to Columns...) and delimit by the "/" and then use the
date funciton to put it back together again. Just some ideas.

Matt

VBA to Fix Dates
 

If you take my example, select the cells, and run the script, you'll see that
they don't change - 0#/0#/####

I tried manually doing what you suggested - changing the format text to
change back to date. The last ten rows of my example turn into the serial
date (I don't know what makes them different). Then, changing them back to
date using the script doesn't do anything except to right-align all the rows
except for those ten. The ill-formatted dates (0#/0#/####) still exist.
I've tried other date formats, so it isn't just a problem with "m/d/yyyy."
If I manually right-click my selection and select format cellscustom, it
formats properly.

I will try to use "/" as a delimiter, throw everything into the date()
function, then copy/paste-value to de-formula-ize them, and see if that works.




matt wrote:
I have to pull data from various databases, who have some crazy date formats.
In most cases, I have to drop the data into Excel anyway, so I created a
[quoted text clipped - 41 lines]
--
Message posted viahttp://www.officekb.com

I took a look at the spreadsheet, and the dates seem to look fine to
me. However, if you want some additional ways to do your
"replacement," consider searching the Date & Time category in the
function box (Menu Bar: Insert/Function) in order to use the existing
data with preset Excel functions. I'd also look up the right, left,
mid, len, search, and substitute functions. You could also create a
For...Next loop and "pick" out the day, month, and year data and put
it together in a date format. You could also do Text to columns (Menu
Bar: Data/Text to Columns...) and delimit by the "/" and then use the
date funciton to put it back together again. Just some ideas.

Matt

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

VBA to Fix Dates
 

I found the problem. Help me fix it, please . . .

It seems that the date format, becomes dependent on the regional settings (
http://write-me.org/screenshots/ShortDateFormat.gif). I think it
automatically interprets it as "Short Date Format" when I really want a
custom date format. I change the settings, and it changes the date format,
even while I have the document open. I think that manually choosing
FormatCells... and telling it Custom makes it understand that I want Custom
instead of ShortDate, whereas Selection.NumberFormat = "m/d/yyyy" doesn't.

So, I end up with a document that could appear appear differently when it is
sent to others! That's no good. I need something instead of Selection.
NumberFormat = "m/d/yyyy"

Thank you.



undrline wrote:
If you take my example, select the cells, and run the script, you'll see that
they don't change - 0#/0#/####

I tried manually doing what you suggested - changing the format text to
change back to date. The last ten rows of my example turn into the serial
date (I don't know what makes them different). Then, changing them back to
date using the script doesn't do anything except to right-align all the rows
except for those ten. The ill-formatted dates (0#/0#/####) still exist.
I've tried other date formats, so it isn't just a problem with "m/d/yyyy."
If I manually right-click my selection and select format cellscustom, it
formats properly.

I will try to use "/" as a delimiter, throw everything into the date()
function, then copy/paste-value to de-formula-ize them, and see if that works.

I have to pull data from various databases, who have some crazy date formats.
In most cases, I have to drop the data into Excel anyway, so I created a
[quoted text clipped - 14 lines]

Matt

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

VBA to Fix Dates
 

Here is my attempt at using SendKeys:

Selection.NumberFormat = "m/d/yyyy"
SendKeys "%o", True ' Format
SendKeys "e", True ' Cells...
' Here's where it gets stuck:
' First, have to hope that the "Numbers" tab is the one showing
' Second, it stops with the dialog open, doesn't tab to the selected entry.
' And, if I try to complete the operation manually, it continues, tab and all,
after I select OK to the dialog.
SendKeys "{TAB}", True
SendKeys "c", True ' date format from previous line; only one C needs to
be pressed
SendKeys "{TAB}", True
SendKeys "m/d/yyyy", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True


undrline wrote:
I found the problem. Help me fix it, please . . .

It seems that the date format, becomes dependent on the regional settings (
http://write-me.org/screenshots/ShortDateFormat.gif). I think it
automatically interprets it as "Short Date Format" when I really want a
custom date format. I change the settings, and it changes the date format,
even while I have the document open. I think that manually choosing
FormatCells... and telling it Custom makes it understand that I want Custom
instead of ShortDate, whereas Selection.NumberFormat = "m/d/yyyy" doesn't.

So, I end up with a document that could appear appear differently when it is
sent to others! That's no good. I need something instead of Selection.
NumberFormat = "m/d/yyyy"

Thank you.

If you take my example, select the cells, and run the script, you'll see that
they don't change - 0#/0#/####
[quoted text clipped - 16 lines]

Matt

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200705/1

VBA to Fix Dates
 

Nevermind. Selection.NumberFormat = "m\/d\/yyyy" fixes it.

undrline wrote:
Here is my attempt at using SendKeys:

Selection.NumberFormat = "m/d/yyyy"
SendKeys "%o", True ' Format
SendKeys "e", True ' Cells...
' Here's where it gets stuck:
' First, have to hope that the "Numbers" tab is the one showing
' Second, it stops with the dialog open, doesn't tab to the selected entry.
' And, if I try to complete the operation manually, it continues, tab and all,
after I select OK to the dialog.
SendKeys "{TAB}", True
SendKeys "c", True ' date format from previous line; only one C needs to
be pressed
SendKeys "{TAB}", True
SendKeys "m/d/yyyy", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True

I found the problem. Help me fix it, please . . .

[quoted text clipped - 17 lines]

Matt

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200705/1