Solved

Excel Date Problem

Posted on 2011-03-02
18
232 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

11 Experts available now in Live!

Get 1:1 Help Now