Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Convert date from dd-mm-yy to mm-dd-yy

This is a discussion on Convert date from dd-mm-yy to mm-dd-yy within the Excel Questions forums, part of the Question Forums category; A friend sent me over a file, but the dates are in "European" format. The cell shows a date as ...

  1. #1
    New Member
    Join Date
    Jan 2006
    Posts
    8

    Default Convert date from dd-mm-yy to mm-dd-yy

    A friend sent me over a file, but the dates are in "European" format. The cell shows a date as "01.10.05" I'd like to convert it to 10/01/05.

    Any ideas on how to do this?

    Thanks.

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,828

    Default

    If "date" is in A1 try this formula in B1

    =SUBSTITUTE(REPLACE(MID(A1,4,3)&A1,7,3,""),".","/")+0

    format as date

  3. #3
    New Member
    Join Date
    Jan 2006
    Posts
    8

    Default

    Barry,

    The formula works.

    Thank you very much!

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,830

    Default

    Select the column, goto Data>Text to columns..., on the 3rd step choose Date as Column data format and DMY from the dropdown.

    Or use Barry's formula.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Feb 2006
    Posts
    4

    Default Convert date from dd-mm-yy to mm-dd-yy

    barry,

    I read your post on how to covert UK/european to US date formats in excel using the following formula

    If "date" is in A1 try this formula in B1

    =SUBSTITUTE(REPLACE(MID(A1,4,3)&A1,7,3,""),".","/")+0

    format as date

    I need to change dates the other way round from US to UK date formats......is the above formula still the same or does it need changing? If so your assistance will be most appreciated in this matter

    I have around 3000+ entries that I need to change to make the information useful for me

    I look forward to receiving your reply

    Regards

    eurodjuro

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,830

    Default

    eurodjuro

    What format are your dates actually in?

    See my post regarding Data>Text to columns...
    If posting code please use code tags.

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,828

    Default

    The formula above was for the specific case where the "dates" weren't actually dates at all but in the text format

    01.10.05

    If you're in the UK and have a worksheet with dates in US format then some of these will probably be recognised as dates, but the wrong date, e.g. 2nd March becomes 3rd Feb and some others, where the day is greater than 12, are not recognised as dates at all, so excel treats these as text.

    This formula will convert the text entries to the correct UK date and the wrong dates to the correct UK dates, assuming the format for your original "dates" of mm/dd/yy or
    mm/dd/yyyy.

    =IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

    If you have variable formats, e.g. with single digit months or days you could use the longer

    =IF(A1="","",IF(ISTEXT(A1),(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1))&LEFT(A1,FIND("/",A1))&RIGHT(A1,2))+0,DATE(YEAR(A1),DAY(A1),MONTH(A1))))

    both of these should work for both US to UK and vice versa

    having said all that I think that Norie's suggestion would also work for you, using MDY as date format, and it would probably be quicker

  8. #8
    New Member
    Join Date
    Feb 2006
    Posts
    4

    Default Convert date from dd-mm-yy to mm-dd-yy

    Chaps,

    Thanks for the replies......I tried the text to column trick but had no impact at all on the data.....even after doing that and trying to format the cells to date and type, it still made no difference. The information I have is downloaded into excel from an online US database.

    For your information, I use a Mac and I dont know if it makes any difference.

    Also, all dates are in the following format: mm/dd/yyyy.

    Will try the formula and see how i get on with that........

    regards

    eurodjuro

  9. #9
    New Member
    Join Date
    Feb 2006
    Posts
    4

    Default

    in fact, the dates are in this formaat:

    m/dd/yyyyand mm/dd/yyyy

    hope that makes sensee

  10. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,828

    Default

    In that case the second formula I posted above should work, used in another column

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com