Solved

Excel Date Problem

Posted on 2011-03-02
18
233 Views
Last Modified: 2012-05-11
I have an excel spreadsheet from another data source (so I can't change it's output) that is giving me dates that I can't use in calculations or reformat. I've tried text to columns, and a number of "formulas" I've found on the web and abolutely nothing has worked for me.

I am stumped! Arrggghh.

Any help would be greatly appreciated.

Thanks,

swjtx99 Date-Problem.xlsx
0
Comment
Question by:swjtx99
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35019047
Run this script from a module and you will be okay :)

Sub Sample()
    With Sheets("Sheet2").Columns("A:A")
        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub

Open in new window


Sid
0
 
LVL 6

Expert Comment

by:akajohn
ID: 35019050
Did you try to format the column as Data. (Right click choose format cells and then choose the date format)

A>
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35019160
I think the problem is that there is a CHAR(160) at the start of each date.

You can get rid of that using Edit > Replace. In the "find what" box hold down ALT key and use the number pad to enter 0160 (you'll see what looks like a space), leave "replace with" box blank and press "Replace All". That will convert to dates....

...or use a formula in B1

=SUBSTITUTE(A1,CHAR(160),"")+0

copied down column

regards, barry
0
 

Author Closing Comment

by:swjtx99
ID: 35019793
You know, I checked to see if there was an arbitrary space on the end but not the beginning....

Good catch. Drove me nuts for a few hours.

Thanks so much!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35019803
swjtx99: Could you tell me what was wrong with my code?

Sid
0
 
LVL 33

Expert Comment

by:Norie
ID: 35019831
I tried Data>Text to Columns... with fixed width and set the first break after the first, non-printing character ie the CHAR(160).

I then select not to import the 1st column and set the column date format for the 2nd column to MDY.

Seemed to work.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35019840
Not for points but for the mere fact that

1) I answered first
2) My code works
3) There was no acknowledgment from the OP towards my post

I am raising a dispute.

Sid
0
 

Author Comment

by:swjtx99
ID: 35020877
Oh. Sorry for not commenting but I did run that first and it didn't work on my sheet.

I'll give it another shot when I get back to the office and repost with what I find.

I could have been mistaken.
0
 

Author Comment

by:swjtx99
ID: 35022408
Hi Sid,

Ok, back in the office. I just tried it again and the leading space on the date is still there after I run the macro. Data is in column A (using the same sheet I attached originally).

I opened the developer tab, opened VB, inserted a module, copied your code in, went back to developer tab, clicked on Macro button, clicked on "Sample" and hit run.

Am I doing something wrong?

Thanks,

swjtx99
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35022439
Here is the sample that you gave.

Please run the Sub Sample in the file.

Sid
Sample.xlsm
0
 

Author Comment

by:swjtx99
ID: 35023155
Ok, Here's what I did. I copied the code you orignally posted to notepad and renamed it "SamplePosted", then copied the code you had in VB in the attachment to the same notepad file and renamed it "SampleInSheet", then copied both into VB in my original sheet as such:

Sub SampleInSheet()
    With Sheets("Sheet2").Columns("A:A")
        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub


Sub SamplePosted()
    With Sheets("Sheet2").Columns("A:A")
        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub


When I run "SamplePosted" it does not work. When I run "SampleInSheet" it does..

I have tried this 100 times with the same result and I see no difference in the actual code

Strangest thing I've ever seen. I open the same file (never save it so it's always the exact one I posted). I do not doubt your solution now but as I said, your original post didn't work and still doesn't.

I really appreciate the help I get in this forum and am not trying to upset anyone. I certainly did try your solution and apologize if I'm doing something to break it.

swjtx99 Date-Problem.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35023161
Does the file that I posted above work?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35023175
Please note that I am least bothered about the points.

I know the code works in the file attached above. When you copy it to notepad, the formatting between the " " changes. You need to copy it direct between VBA Editors.

Anyways I am withdrawing the dispute.

Sid
0
 

Author Comment

by:swjtx99
ID: 35023338
Yes sir, the file you posted works.

I still don't understand it completely. The file works but not the code in your original post if I copy/paste that into VB. (not going through notepad)

I really do not know what I am doing so I probably broke it and I'm really at a loss but just wanted you to know that I didn't overlook your solution and apologize for whatever I'm doing to cause it not to work.

swjtx99
0
 
LVL 33

Expert Comment

by:Norie
ID: 35023714
Sid

It's not a space, it's the non-printing character with the ASCII value of 160.

In your file, and code, you've used that but it isn't really clear.

You could use Chr(160) instead of " ", which might make it a bit clearer.

  .Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033124
@swjtx99: That's ok :)

What must have happened is when I posted the code from VBA Editor into this post, it must have lost it's formatting and probably that is the reason why it still works in the file but not when you copy it from above. As Barry and imnorie suggested you may use Chr(160).

@imnorie: I know it is not a space ;) Else the Excel file wouldn't have worked :)

Sid
0
 
LVL 33

Expert Comment

by:Norie
ID: 35033661
Sid

It would still be a good idea to use Chr(160), makes it clearer that is the character being used and not a space.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033668
That is exactly what I suggested in ID: 35033124.

Sid
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now