Solved

Customer Excel Data Format

Posted on 2014-02-27
4
278 Views
Last Modified: 2014-02-27
Hello,
I have data that contains time stamps in this format:

Thu 9 Jan 00:01:24
Fri 10 Jan 23:33:29

I'm trying to put a customer date format on it so I can group the data.  I tried:
ddd d mmm hh:mm:ss
but it doesn't seem to work.  Can someone help me put the right format in please?
Thanks,
Tod
0
Comment
Question by:tkeiffer
[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
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39893711
Try using the Data...Text to Columns menu item to convert your time stamps into date/time serial numbers that you can format as desired.

1.  Select the cells
2.  Open the Data...Text to Columns menu item
3.  Choose Fixed in the first step of the wizard
4.  In the third step of the wizard, choose to not import the first field, and to import the second field as a Date in DMY format
5.  Format the results to suit

Macro equivalent to steps 1 through 4:
Sub ConvertDates()
    Selection.TextToColumns Destination:=Selection.Cells(1, 1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(3, 4)), TrailingMinusNumbers:=True
End Sub

Open in new window

0
 

Author Comment

by:tkeiffer
ID: 39893770
Hello,
The problem with the text to columns using a fixed type, is that the 1-9 dates do not have a zero before them so the fixed length is different.  While I was waiting for a response to this question I did a find/replace for space 1 space with a space 01 space, and did that through 9.  I then did a text to columns and this worked.  However, I need something more practical because I'm creating a template for someone to use and it needs to be somewhat simple.  The macro works great and converts the data to a format where excel recognizes it as a time stamp.  Thank you very much.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39893789
The problem with the text to columns using a fixed type, is that the 1-9 dates do not have a zero before them so the fixed length is different.
The suggested macro was recorded when I used the TextToColumns manual method. The wizard put the fixed field break after the third character, so you got a leading space before both single and double digit dates in the other field. The converter uses the rest of the data as the second field, so you don't need to specify its length. The converter also ignores the leading space when doing the conversion.

Brad
0
 

Author Comment

by:tkeiffer
ID: 39893798
You're the man.  I never knew the rule about the leading space.  That would have saved me some time throughout the years that's for sure!  Thanks again.
0

Featured Post

Industry Leaders: 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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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