Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Date Problem

Posted on 2011-03-02
18
Medium Priority
?
242 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 36

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

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 36

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

564 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