Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Customer Excel Data Format

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
tkeiffer
Asked:
tkeiffer
  • 2
  • 2
1 Solution
 
byundtCommented:
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
 
tkeifferAuthor Commented:
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
 
byundtCommented:
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
 
tkeifferAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now