Help!! Excell Gurus....

Discussion in 'OT Technology' started by aredee, Jun 8, 2006.

  1. aredee

    aredee New Member

    Joined:
    Aug 20, 2002
    Messages:
    1,043
    Likes Received:
    0
    Location:
    Washington DC
    So my stupid coworker, before he left, entered into a excell spredsheet the date wrong!!

    19280811
    19181016
    19280307
    19571016


    yyyymmdd

    is there anyway we can change this to mmddyyyy?
     
  2. crontab

    crontab (uid = 0)

    Joined:
    Nov 14, 2000
    Messages:
    23,454
    Likes Received:
    12
    Dunno if you can do that in excel without a delimiter.

    Got perl?

    export that column to text

    cat file.txt | perl -pe 's/(....)(..)(..)/$2$3$1/'

    enter that column back in.

    If there is an excel solution I would like to know it too.
     
  3. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    This is possible using VBA, methinks.

    Treat the cell as a string, read them one by one, strip the first 4, and then slap em on the end, then assign the vale back to the cell.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    =concatenate(mid(a1,5,2),mid(a1,7,2),mid(a1,1,4))

    You can convert it into a date format as well:

    =date(mid(a1,1,4),mid(a1,5,2),mid(a1,7,2))
     
  5. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    You know, your "stupid" coworker actually was entering dates in the best format for being able to sort them: year comes first, then month, then day. However, I have no idea whether it would be easy or hard to adapt the rest of your spreadsheet to use this better format. (shrug)

    You should seriously consider migrating your spreadsheets to Access or SQL databases; it reduces the amount of redundant data, and it allows for tighter control over the way data is typed in. (my company was recently paid a nice sum of money to have me migrate a set of accounting spreadsheets for the navy. god they were some heinous spreadsheets.)
     
  6. crontab

    crontab (uid = 0)

    Joined:
    Nov 14, 2000
    Messages:
    23,454
    Likes Received:
    12
    saved
     
  7. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    anybody else find it ironic that he calls his coworker stupid while he spells "Excel" as "Excell" and doesn't seem to want to use "an" before words that start with a vowel?
     
  8. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    (grin)

    Come on now, we all know that "stupid" really means "not primarily concerned with my preferences". That's why there are so many stupid drivers in the world; almost none of them care about what I want to do.
     

Share This Page