0

I am working with a column of data which contains a date/time stamp of an event. The printed form is in US format, as follows:

12/25/2014 11:00 AM

The cells are formatted as "General". I'm looking to convert the column into a Date / Time field for ease of filtering and searching. Thus far the only way I've been able to convert the text is to use "Text to Columns" and import the columns delimited by a Space, but this hasn't gotten me exactly what I want as it imports the date perfect, but the time is still text and in another column, which isn't pretty.

Is there an easier way? Thanks in advance

  • What are your windows regional settings for date? And also, is the data a text string? e.g., if you execute `ISTEXT(cell_ref)`, what is the result? – Ron Rosenfeld Aug 03 '15 at 11:27

2 Answers2

0

You should try custom formatting of date and time.

  1. If working with Google spreadsheet see this link.
  2. If working with excel see this link.
Community
  • 1
  • 1
Irum Manzoor
  • 189
  • 1
  • 5
  • Thanks Irum - I tried that and no joy - changing to date, number, custom formatting etc does not seem to make any change to the data. Only if I use text-to-columns first and lose the time stamp. –  Aug 03 '15 at 10:22
0

Since these values are apparently text strings, you won't be able to change the values by formatting. Excel stores dates (and times) as days and fractions of a day since 1/1/1900. You have, I believe, four non-VBA options:

  • Change the data before you enter into your worksheet, to be in the same format as your Windows Regional Settings short date (perhaps DMY?)

  • Change your Windows Regional Settings short date format to match that of the data you are importing.

  • Split the data using Text to Columns as you have been, then combine the two fields with an addition. e.g.

    • Original data in A1
    • Split data in A1:B1
    • Combine -- C1: =A1+B1
    • Copy C1; PasteValues (C1), then delete A1:B1
  • Use a formula to separate the different segments. The following assumes your Windows Regional settings AM/PM designation is, indeed, AM/PM, otherwise the formula would have to also account for that. Also, if the format is mm/dd/yyyy hh:mm AM/PM then the formula could be simplified. The formula below assumes the format is m/d/yyyy h:mm AM/PM and that AM/PM works in your locale.
=DATE(
MID(A1,FIND("/",A1,4)+1,4),
LEFT(A1,FIND("/",A1)-1),
MID(A1,FIND("/",A1)+1,FIND("/",A1,4)-FIND("/",A1)-1)
)
+TRIM(MID(A1,FIND(":",A1)-2,9))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thanks Ron. Yes, confirmed they are text strings, they are exported into excel by a system so I have no control on the format I receive them in. However, splitting and combining as you've suggested is a good workaround, I'll package that up in a nice macro and go with that. Also, the formula option worked just as well, so thanks for the detailed reply. –  Aug 03 '15 at 12:14