Excel won't recognize text as dates

[jent]

I have an Excel spreadsheet with three columns and a few hundred rows. One of the columns is "Date," with the date in the following format: M/D/YYYY H:MM PM EST, which I believe Excel only sees as text, not as actual dates.

Unfortunately, the data in the spreadsheet is the output of analytics software that doesn't allow me to customize the output. Because I can't figure out how to either reformat the date as YYYY/MM/DD or get Excel to recognize the dates and sort them accordingly without reformatting, trying to sort by date does not actually sort by date.

Here's an example of a few rows' worth of dates incorrectly sorted by Excel:

1/29/2011 2:07 PM EST
1/31/2009 5:21 PM EST
1/6/2012 6:49 PM EST
1/6/2012 8:34 AM EST
10/11/2008 6:34 AM EDT

You'll notice that the years are out of order, and after January (month "1") ends, October (month "10") follows instead of February. So it's not even accurately sorting by month. That being said, I want complete date sorting, in full chronological order.

Any help is greatly appreciated. Thank you!

 

[GGJstudios]

Those aren't valid date formats for Excel. To get them into the proper format, you'll have to use "Text to Columns" to separate the date from the time and the AM/PM and the time zone. Then you'll have to format each column appropriately.

You might also be able to parse them using the DATEVALUE formula.

http://www.yogeshguptaonline.com/2009/10/excel-convert-text-to-date.html
Convert dates stored as text to dates - Excel - Office.com

 

[jent]

Thank you! I had to play around with the settings a few times to get it working right, but that's just what I was looking for.

Now is there a way to automate this process? I'll likely be doing it on a weekly basis, so being able to automate it, take the final output and copy and paste the new content into a master document would really streamline this process.

Thanks for any further advice.

 

[GGJstudios]

Thank you! I had to play around with the settings a few times to get it working right, but that's just what I was looking for.

Now is there a way to automate this process? I'll likely be doing it on a weekly basis, so being able to automate it, take the final output and copy and paste the new content into a master document would really streamline this process.

Thanks for any further advice.

You may be able to set up a script to do that.

 

[jaduff46]

If you want to stay in Excel, the following works and doesn't do a lot of brain damage.

1. Location of 1st blank in input string

2. DateValue of input up to (1)

3. TimeValue of input beginning at (1) up to (but excluding timezone).

4. = (2) + (3) formatted as an Excel date


Since (4) is an Excel date, you could create a macro to further process the data (i.e. sort based on (4)).

 

[James_C]

Building on GGJstudios excellent reply you can use this formula to convert the text string. The DateValue function will work if you just remove the last 4 characters of the text string, then it will recognise the text as a valid date string.

The formula is =DATEVALUE(MID(A1,1,(LEN(A1)-4)))

Replace A1 with the cell containing the date text.

This formula just sorts on the date and will ignore the time component in the text string.



One little short cut that you might find useful, if you don't already know it. If you enter a formula adjacent to a column that contains formula or data, if you double click the drag handle in the bottom right corner of the cell it will automatically copy the formula down for you.



If you need to sort on date and time use this formula instead;

=DATEVALUE(MID(A1,1,(LEN(A1)-4)))+TIMEVALUE(MID(A1,1,(LEN(A1)-4)))



Just a quick note on date formats, you might notice that in the screenshot I posted I switched the months and days around. This is because I am based in the UK, and Excel needs to have the text string in the local date format (dd/mm/yyyy). The formula will work fine with your dates.

I hope this helps

 

[Gregg2]

Here's an example of a few rows' worth of dates incorrectly sorted by Excel:

1/29/2011 2:07 PM EST
1/31/2009 5:21 PM EST
1/6/2012 6:49 PM EST
1/6/2012 8:34 AM EST
10/11/2008 6:34 AM EDT

That's actually sorted "alphabetically". Read it vertically. Using only the relevant digits:
13
1620126
1620128
10

I'm not sure why 0 is last when it follows another number, because I'm pretty sure that if you had a leading 0, it would be first.

Must have been sleep deprived when I typed that.

 

[GGJstudios]

That's actually sorted "alphabetically". Read it vertically. Using only the relevant digits:
13
1620126
1620128
10

I'm not sure why 0 is last when it follows another number, because I'm pretty sure that if you had a leading 0, it would be first.

Since it's being sorted as text, the relevant digits would be:
1/2
1/3
1/6/2012 6
1/6/2012 8
10

 

[VoxPanther]

Those aren't valid date formats for Excel. To get them into the proper format, you'll have to use "Text to Columns" to separate the date from the time and the AM/PM and the time zone. Then you'll have to format each column appropriately.

You might also be able to parse them using the DATEVALUE formula.

http://www.yogeshguptaonline.com/2009/10/excel-convert-text-to-date.html
Convert dates stored as text to dates - Excel - Office.com

Thanks for the datevalue tip. I couldn't remember it and was looking for it. Mac people are truly superior.