Announcement

Collapse
No announcement yet.

How to change month name into number?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to change month name into number?

    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.

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        Sorry

        misunderstood thread.

        I'll have a think about that one there must be a way.

        Tim.

        Comment


        • #5
          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

          Comment


          • #6
            mhabib - it does not work....try it for yourself, but thank you anyway...

            Comment


            • #7
              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

              Comment


              • #8
                How about using:

                =MONTH(DATEVALUE(A1))

                (assuming the string is in A1).

                Regards,
                Barrie Davidson
                My Excel Web Page
                "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

                Comment


                • #9
                  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

                  Comment

                  Working...
                  X