Converting Date DD-MMM-YYYY to DD/MM/YYYY

  • Hi there,

    Could someone please help me in converting the date format DD-MMM-YYYY to DD/MM/YYYY?

    I've tried using text to column, followed by the logical if function, then proceedding with concatenating but it doesn't work as I think I'm limited to a certain amount of logical test.

    This is what I've done:

    Current format:

    11-MAY-2009



    Text to column gives:

    11
    MAY
    2009



    Then proceeding with the logical Test:

    =IF(B2="JAN";"01";IF(B2="FEB";"02";IF(B2="MAR";"03";IF(B2="AVR";"04";IF(B2="MAY";"05";IF(B2="JUN";"06";IF(B2="JUL";"7";IF(B2="AUG";"8";"00"))))))))

    And finallt concatenating gives:

    =A2&"/"&D2&"/"&C2 --> 11/05/2009

    but I'm limited with the logical test :(

    Any other alternative?

    Thanks,

    Jeremie Henry

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    using vba:

    Code
    Range("A1").Value = Format(Range("A1").value, "dd/mm/yyyy")


    Or by formatting the cell by right clicking, selecting Date, and adjusting the parameters

    Regards

    Attila

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    Hi again..

    Another quick question, how do I use the macro above if I have to convert one whole row/column of data (date in the DD/MMM/YYYY fomat).

    the code only works for individual cell.

    Thanks.

    Jeremie.

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    As Attila mentioned, you don't need a macro to do this. Highlight the column, hit CTRL +1, go to "Date" and find your format, or go to "Custom" and enter "dd/mm/yyyy"

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    Thanks for the reply but excel doesn't recognise DD-MMM-YYYY as a date format when selecting the Ctrl+1 and attempting to change the format from there.

    Any other solution please? Could you please customize the script above so that it will work for rows/column?

    Thanks,

    Jeremie.

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    In your example the month name is in English (May not Mai), is that true for all dates? What does 1st May look like, is it 01-MAY-2009 with a leading zero or 1-MAY-2009?

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    If you are going to select the row column before running the macro:

    Code
    Selection.Value = Format(Selection.Value, "dd/mm/yyyy")


    If you already know which columns use Range("A:A") or for rows Range("1:1") instead of Selection


    Regards


    Attila

  • Re: Converting Date DD-MMM-YYYY to DD/MM/YYYY


    Thanks for the answer Atttila.

    I've tried running the macro you've provided but receiving the error message below:

    Runtime error 13, type mismatch.

    Code
    Range("E:E").Select
    Selection.Value = format(Selection.Value, "dd/mm/yyyy")



    Do you know what I am doing wrong?

    Thanks once more for your help,

    Jeremie

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!