How to change month name into number?

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi there,


    I have the following problem,


    I have a list of dates with the following format (in the string):


    "07 July 2004"


    I want to transfer this into a date (07.07.2004) but I have no idea how to transfer the extracted month name (July in this example) into a number (07).


    Any idea?


    Tnx a lot in advance.

    Hi Maxmills,


    put your cursor over the date and do the following -


    Format
    Cells
    Category - Custom
    Type dd.mm.yyyy




    Thats should do you.


    Tim.

    Timbo,


    if that was the case I would not make this post... The name of the month is a text string and is extracted as such - "june" first. Second, the cell contains as I specified spaces between the date, the month and the year that's why Excel could not recognise it as a date. The one way I could do this is to make another sheet with the names of the months and the number of each month in the neighbour cell, and then link the text string to the number, but I guess that there may be a formula transferring the text string "june" into 6, which was my question.

    Hi maxmills,


    You can run up the "Data > Text To Columns" command.


    1. Select your dates
    2. Click Data > Text to Columns
    3. Select the "Delimited" option in step 1 of the Wizard and click Next
    4. Click Next again
    5. In step 3 of the wizard, select "Date" under Column Data format section, with the DMY style
    6. Click Finish


    HTH


    m

    Hi,


    A procedure or a function should do it.


    Dim date1 as integer, date2 as integer, date3 as integer, temp
    date1 = left(yourCell,2)
    date3 = right(yourcell,4)
    temp = left(yourcell, len(yourCell)-4)
    temp = right(temp, len(temp - 2))
    temp = trim(temp)
    yourCell = date1 & "." & date2 & "." & date3


    You might have to mess around with the formatting to get it to show as you want it to.


    John

    Hi,


    I tried the Text-To-Columns command for a range of dates (with and without quote marks). It worked fine.


    Perhaps I haven't correctly understood what your requirement is. It appears from your post that you would like a date such as:


    "07 July 2004"


    To appear as:


    07.07.2004


    If this is what you want, then Text-To-Columns will convert a text entry into a TRUE date. The next step would be to apply the date format of your choice ("dd.mm.yyyy").


    HTH,


    m

Participate now!

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

Register Yourself Login