Excel won't recognize text as dates

Discussion in 'Mac Apps and Mac App Store' started by jent, Feb 24, 2012.

Most Liked Posts
  1. jent, Feb 24, 2012

    jent macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #1
    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:
    Code:
    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!
     
    share
    + Quote Reply
  2. GGJstudios, Feb 24, 2012

    GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #2
    share
    + Quote Reply
  3. jent, Feb 24, 2012

    jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #3
    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.
     
    share
    + Quote Reply
  4. GGJstudios, Feb 24, 2012

    GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #4
    You may be able to set up a script to do that.
     
    share
    + Quote Reply
  5. jaduff46, Feb 24, 2012
    Last edited: Feb 24, 2012

    jaduff46 macrumors 6502

    jaduff46

    Joined:
    Mar 3, 2010
    Location:
    Second star on the right....
    #5
    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)).
     

    Attached Files:

    share
    + Quote Reply
  6. James Craner, Feb 24, 2012
    Last edited: Feb 25, 2012

    James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #6
    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.

    Microsoft Excel.jpg

    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.

    Microsoft Excel copy formula down.jpg

    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)))

    Microsoft Excel full Date and Time.jpg

    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
     
    share
    + Quote Reply
  7. Gregg2, Feb 24, 2012
    Last edited: Jan 9, 2013

    Gregg2 macrumors 603

    Joined:
    May 22, 2008
    Location:
    Milwaukee, WI
    #7
    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.
     
    share
    + Quote Reply
  8. GGJstudios, Feb 24, 2012

    GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #8
    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
     
    share
    + Quote Reply
  9. VoxPanther, Jan 8, 2013

    VoxPanther macrumors newbie

    Joined:
    Aug 18, 2012
    #9
    Thanks for the datevalue tip. I couldn't remember it and was looking for it. Mac people are truly superior.
     
    share
    + Quote Reply
(You must log in or sign up to post here.)

Share This Page