VBA to Fix Dates

U

undrline

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.
 
M

matt

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.
Click to expand...

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
 
U

undrline via OfficeKB.com

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 cells>custom, 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
Click to expand...
[quoted text clipped - 41 lines]
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
Click to expand...
 
U

undrline via OfficeKB.com

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
Format>Cells... 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#/####

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 cells>custom, 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.
[quoted text clipped - 14 lines]
Matt
Click to expand...
Click to expand...
 
U

undrline via OfficeKB.com

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 . . .

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
Format>Cells... 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#/####
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
U

undrline via OfficeKB.com

Nevermind. Selection.NumberFormat = "m\/d\/yyyy" fixes it.
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 . . .
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Find and Replace Help 3
Help with a loop please. 10
Macro to select columns and replace data 6
Need help with a "If" "Else" VB code 4
Can I improve processing speed of macro 4
Slow Macro 7
Skip or Ignore Missing Worksheets 1

Top