Solved

Excel Date Problem

Posted on 2011-03-02
18
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 34

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 34

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 34

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

691 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