+ Reply to Thread
Results 1 to 19 of 19

Changing format of dates in a column

  1. #1
    Registered User
    Join Date
    04-20-2011
    Location
    Hull, England
    MS-Off Ver
    Excel 2010
    Posts
    79

    Question Changing format of dates in a column

    Hello,

    I am working with a 2010 Excel spread sheet and need some direction.

    One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.

    I need all of the dates to be in the first format (dd/mm/yyyy).

    Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it? LOL!

    Any thoughts?

    Cheers,

    Dave

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Changing format of dates in a column

    Dave that look's like that your dates are not real dates but text that looks like dates. For test it do this.

    If your "dates" are in column A, try in Column B this.

    =ISNUMBER(A2)

    If this gives FALSE then is text.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing format of dates in a column

    Perhaps upload a sample of your date? (I think Fotis hit the nail on the head though)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Changing format of dates in a column

    Try this...

    Select the range of cells in question.

    Press the key combo of CTRL H. This will open the Find/Replace userform.

    Find what: st
    Replace with: nothing, leave this empty
    Replace All

    Repeat the process for the other ordinals nd, rd, and th.

    Format the cells in the date format of your choice.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    04-20-2011
    Location
    Hull, England
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Changing format of dates in a column

    Quote Originally Posted by Fotis1991 View Post

    If this gives FALSE then is text.
    Looks like it is giving me a 'false'.

    I have attached a sample of what I am up against.

    Any thoughts?

    Thanks for the input!

    Dave
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Changing format of dates in a column

    Im pretty sure Tony (or someone else) will improve on this, but until they do, give this a shot in a helper column...
    =IF(ISNUMBER(A1),A1,DATEVALUE(LEFT(A1,FIND(" ",A1,1)-3)&MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,FIND(" ",A1,1)+1)-1)-FIND(" ",A1,1))&RIGHT(A1,4)))

    (I tried the Text2Columns trick, it didnt work)

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Changing format of dates in a column

    As you have some blank entries in column A, here's another one in B1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Format B1 as a date, then copy down to the bottom of your list. I noticed that you have one year as 19661, so this produces an error.

    Hope this helps.

    Pete

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Changing format of dates in a column

    another
    =IFERROR(DATEVALUE(REPLACE(A1,FIND(" ",A1)-2,2,"")),A1)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Registered User
    Join Date
    04-20-2011
    Location
    Hull, England
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Changing format of dates in a column

    Quote Originally Posted by Pete_UK View Post
    As you have some blank entries in column A, here's another one in B1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Format B1 as a date, then copy down to the bottom of your list.
    I'm not real sure were to put this.......did it work for you?



    Quote Originally Posted by FDibbins View Post
    Im pretty sure Tony (or someone else) will improve on this, but until they do, give this a shot in a helper column...
    =IF(ISNUMBER(A1),A1,DATEVALUE(LEFT(A1,FIND(" ",A1,1)-3)&MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,FIND(" ",A1,1)+1)-1)-FIND(" ",A1,1))&RIGHT(A1,4)))

    (I tried the Text2Columns trick, it didnt work)
    Thanks for the advice...when I tried this I got some weird stuff. Did it work for you?

    Still trying.......any thoughts?

    Thanks for the help everyone. I am still trying to sort this out.

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Changing format of dates in a column

    hmm all work for me
    here are all 3 formula approaches
    Attached Files Attached Files
    Last edited by martindwilson; 10-20-2013 at 06:48 AM.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Changing format of dates in a column

    Quote Originally Posted by wisedave View Post
    I'm not real sure were to put this.......did it work for you?
    I put my formula in cell B1 (stated twice in my post), then copied down to the bottom of your data.

    Hope this helps.

    Pete
    Attached Files Attached Files

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Changing format of dates in a column

    No formulas needed.

    The suggestion in post #4 does what you want.

    However, there are several invalid date entries that you'll have to fix manually.

    22 January 19661
    06/01/1698
    16/5/1657
    16//9/1978
    24//11/1981
    24/06/1659

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Changing format of dates in a column

    Quote Originally Posted by Tony Valko View Post
    No formulas needed.

    The suggestion in post #4 does what you want.
    This does not work for me for all cases. Both suggested formulas work great.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Changing format of dates in a column

    Worked for me except for the cases I noted which are invalid date entries.

    Which formulas converted these to proper dates:

    22 January 19661
    06/01/1698
    16/5/1657
    16//9/1978
    24//11/1981
    24/06/1659

    Here are the results I get (in Excel 2007) using 3 of the posted formulas:

    Data Range

    A
    B
    C
    D
    1
    22 January 19661
    #VALUE!
    #VALUE!
    22 January 19661
    2
    06/01/1698
    #VALUE!
    #VALUE!
    06/01/1698
    3
    16/5/1657
    #VALUE!
    #VALUE!
    16/5/1657
    4
    16//9/1978
    #VALUE!
    #VALUE!
    16//9/1978
    5
    24//11/1981
    #VALUE!
    #VALUE!
    24//11/1981
    6
    24/06/1659
    #VALUE!
    #VALUE!
    24/06/1659


    Formula entered in column B:

    =IF(ISNUMBER(A1),A1,DATEVALUE(LEFT(A1,FIND(" ",A1,1)-3)&MID(A1,FIND(" ",A1,1)+1,(FIND(" ",A1,FIND(" ",A1,1)+1)-1)-FIND(" ",A1,1))&RIGHT(A1,4)))

    Formula entered in column C:

    =IF(A1="","",IF(ISNUMBER(A1),A1,DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"th",""),"rd",""),"nd",""),"st",""))))

    Formula entered in column D:

    =IFERROR(DATEVALUE(REPLACE(A1,FIND(" ",A1)-2,2,"")),A1)

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Changing format of dates in a column

    This seems to work. Enter in B1 and copy down the length of the data.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Changing format of dates in a column

    Quote Originally Posted by newdoverman View Post
    This seems to work.

    =IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd",""),"rd",""),"th",""))+0,"")
    Doesn't work on the entries I noted in my earlier reply.

    Here are their locations if you want to check:

    A81: 22nd January 19661
    A370: 06/01/1698
    A586: 16/5/1657
    A694: 16//9/1978
    A730: 24//11/1981
    A1377: 24/06/1659

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Changing format of dates in a column

    None of those are "legal" Excel dates however, another substitute could take care of the // that appears in two of the "dates". All of the above cited dates were left with a blank rather than an error.

    Those dates would have to be edited to what was really intended. Who knows what was intended by the 1698, 1657, and 1659 dates. I wouldn't personally wouldn't trust an automated "correction" preferring to manually confirm what was actually intended. (got stung on this very type of thing in a massive data dump of the creation dates of over a million files).

    I should probably left the IFERROR out and let the errors show as errors.

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Changing format of dates in a column

    Quote Originally Posted by newdoverman View Post
    ... another substitute could take care of the // that appears in two of the "dates"...
    Agreed. It is also possible to use substitute to change /16 to /19, as with UK-format dates there is no occasion that this would conflict with the days.

    ...I should probably left the IFERROR out and let the errors show as errors...
    Which is why I left it out of my formula in Post #7 - very similar to yours. You can apply a filter to the column and select errors from the bottom of the pull-down to focus in on them. I have no idea as to whether 19661 should be 1961 or 1966, so that would need manual adjustment.

    Hope this helps.

    Pete

  19. #19
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Changing format of dates in a column

    One thing that I did learn (in my working days) was to treat each import separately to get the format of things like dates correct before combining with other imports. It seems that each source has its own quirks that are more easily dealt with on their own than trying to sort things out after a merge.

    My impression of this data was that it came from at least two sources.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trouble changing the format of dates in a chart
    By SEMMatt in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-18-2013, 09:07 AM
  2. Average hours between dates & changing format
    By mrcois in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-02-2010, 06:25 AM
  3. Changing the format of dates
    By davewoodville in forum Excel General
    Replies: 8
    Last Post: 04-13-2010, 08:35 AM
  4. [SOLVED] Dates keep changing to US format when mail merging
    By SEAN DI''''ANNO in forum Excel General
    Replies: 0
    Last Post: 10-13-2005, 12:05 PM
  5. [SOLVED] Dates Format changing when copying data between workbooks
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2005, 02:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1