Closed Thread
Results 1 to 5 of 5

how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

  1. #1
    Jack Wilson
    Guest

    how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

    I have a spreadsheet that the data, the date was input in Excel 2003 as
    mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
    "dd:mm:yyyy"(31:12:2004)? I tried to change date
    format from "Format Cells----choose "date" or "custom" and tried different
    options, but they wouldnt change the data already in the cells. I have a
    spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
    colons) format.

    Thanks

    Jack



  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    How about

    =TEXT(SUBSTITUTE(E1,":","/"),"dd/mm/yy")

    assuming date in E1

    VBA Noob

  3. #3
    Dave Peterson
    Guest

    Re: how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

    How about a custom format of:

    dd\:mm\:yyyy



    Jack Wilson wrote:
    >
    > I have a spreadsheet that the data, the date was input in Excel 2003 as
    > mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
    > "dd:mm:yyyy"(31:12:2004)? I tried to change date
    > format from "Format Cells----choose "date" or "custom" and tried different
    > options, but they wouldnt change the data already in the cells. I have a
    > spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
    > colons) format.
    >
    > Thanks
    >
    > Jack


    --

    Dave Peterson

  4. #4
    SteveW
    Guest

    Re: how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

    What you've done should work

    Don't forget it wont change the data.
    So if you click on one of the date cells it will still say 12/31/2004
    But the format will display the date on screen/print as you want.

    Steve

    On Mon, 17 Jul 2006 23:11:02 +0100, Jack Wilson <Jack =

    <[email protected]>> wrote:

    > I have a spreadsheet that the data, the date was input in Excel 2003 a=

    s
    > mm/dd/yyyy (12/31/2004), how can I convert the column of cells format =

    to
    > "dd:mm:yyyy"(31:12:2004)? I tried to change date
    > format from "Format Cells----choose "date" or "custom" and tried =


    > different
    > options, but they wouldnt change the data already in the cells. I have=

    a
    > spreadsheet that Microsoft is requesting information in dd:mm:yyyy (ye=

    s
    > colons) format.
    >


  5. #5
    Jack Wilson
    Guest

    RE: how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel

    Thanks to all for the replies. I appreciate it. -Jack

    "Jack Wilson" wrote:

    > I have a spreadsheet that the data, the date was input in Excel 2003 as
    > mm/dd/yyyy (12/31/2004), how can I convert the column of cells format to
    > "dd:mm:yyyy"(31:12:2004)? I tried to change date
    > format from "Format Cells----choose "date" or "custom" and tried different
    > options, but they wouldnt change the data already in the cells. I have a
    > spreadsheet that Microsoft is requesting information in dd:mm:yyyy (yes
    > colons) format.
    >
    > Thanks
    >
    > Jack
    >
    >


Closed Thread

Thread Information

Users Browsing this Thread

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

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