Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: How to change month name into number?

  1. #1
    Join Date
    20th April 2004
    Posts
    11

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th April 2004
    Posts
    11
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047
    Sorry

    misunderstood thread.

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

    Tim.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th June 2003
    Location
    Canada
    Posts
    301
    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    20th April 2004
    Posts
    11
    mhabib - it does not work....try it for yourself, but thank you anyway...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th March 2003
    Location
    Winnipeg, Manitoba, Canada
    Posts
    84
    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

  9. #9
    Join Date
    24th June 2003
    Location
    Canada
    Posts
    301
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Change/Format Date To Month Name
    By Abi in forum EXCEL HELP
    Replies: 2
    Last Post: November 26th, 2008, 00:07
  2. Change Formula Reference Each Month
    By jwilso6 in forum EXCEL HELP
    Replies: 9
    Last Post: March 25th, 2008, 08:18
  3. Insert Row At Every Change In Month
    By Prem. in forum EXCEL HELP
    Replies: 10
    Last Post: October 2nd, 2007, 20:00
  4. change dates as month change
    By newengwong in forum EXCEL HELP
    Replies: 1
    Last Post: July 26th, 2006, 02:32
  5. Change month in text
    By linkin_anytime in forum EXCEL HELP
    Replies: 8
    Last Post: October 12th, 2005, 20:00

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