locked
format dates from dd/mm/yyyy to mm/dd/yyyy format RRS feed

  • Question

  • Hi,

     

    I have downloaded an excel file with some columns that have the following date format: dd/mm/yyyy and I would

    like to convert all these dates to English(US) date format :mm/dd/yyyy.

     

    What is the easiest way of converting these dates?

     

    Many thanks!!!!

     


    Rik
    Monday, April 11, 2011 11:06 AM

Answers

  • If this is a one off, and relatively easy to find your dates, simplest is to
    do it manually -
     
    Select the first set of dates
    Press Ctrl 1 to show the format cells dialog
    Select Date, if the list is not in US format select it in the "Locale" drop
    down.
    Apply your preferred date number-format
    User the format painter to change other Date cells
     
    Peter Thornton
     
     
    • Proposed as answer by Carlos Mallen Monday, April 11, 2011 8:20 PM
    • Marked as answer by Calvin_Gao Monday, April 18, 2011 5:56 AM
    Monday, April 11, 2011 12:25 PM
  • Hi,

    If you're programming with VBA, you can write some thing like

    Dim wks As Excel.Worksheet
    Set wks = Worksheets(1)
    
    Dim dateRange As Excel.Range
    Set dateRange = wks.Cells(1, 1)
    
    dateRange.NumberFormat = "mm-dd-yyyy"
    

    Kind regards,

    Carlos Mallen

    • Proposed as answer by Calvin_Gao Wednesday, April 13, 2011 8:29 AM
    • Marked as answer by Calvin_Gao Monday, April 18, 2011 5:55 AM
    Monday, April 11, 2011 8:24 PM

All replies

  • If this is a one off, and relatively easy to find your dates, simplest is to
    do it manually -
     
    Select the first set of dates
    Press Ctrl 1 to show the format cells dialog
    Select Date, if the list is not in US format select it in the "Locale" drop
    down.
    Apply your preferred date number-format
    User the format painter to change other Date cells
     
    Peter Thornton
     
     
    • Proposed as answer by Carlos Mallen Monday, April 11, 2011 8:20 PM
    • Marked as answer by Calvin_Gao Monday, April 18, 2011 5:56 AM
    Monday, April 11, 2011 12:25 PM
  • hi,

    =DATE (year, month, day)

    =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-11 07:06, Cire1507 a écrit :

    Hi,



    I have downloaded an excel file with some columns that have the following date format: dd/mm/yyyy and I would

    like to convert all these dates to English(US) date format :mm/dd/yyyy.



    What is the easiest way of converting these dates?



    Many thanks!!!!


    Monday, April 11, 2011 12:39 PM
  • Hi,

    If you're programming with VBA, you can write some thing like

    Dim wks As Excel.Worksheet
    Set wks = Worksheets(1)
    
    Dim dateRange As Excel.Range
    Set dateRange = wks.Cells(1, 1)
    
    dateRange.NumberFormat = "mm-dd-yyyy"
    

    Kind regards,

    Carlos Mallen

    • Proposed as answer by Calvin_Gao Wednesday, April 13, 2011 8:29 AM
    • Marked as answer by Calvin_Gao Monday, April 18, 2011 5:55 AM
    Monday, April 11, 2011 8:24 PM