+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : need formula to recognise a date and convert to text in another cell

  1. #1
    Registered User
    Join Date
    10-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    11

    need formula to recognise a date and convert to text in another cell

    I have a spreadsheet which has several headings including date (i.e. 11.01.2012) and Month (i.e Jan)

    I was looking for a formula which will recognise a date and automatically fill in the "month" cell based on the date text entered. Is this possible?

    The point of this is that i want to be able to filter info from different months and have this info copied into another tab.

    PLEASE HELP!!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: need formula to recognise a date and convert to text in another cell

    You could just reference the cell with the date and use a Custom format ("mmmm") if you only want the cell to display the month.

    Or, you could use TEXT, for example: =TEXT(A2,"mmmm") ... if you want the text to be used in a comparison, maybe.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: need formula to recognise a date and convert to text in another cell

    the date is already in the format dd/mm/yyyy - I need to be able to see exact date in one column and MONTH only in another column. Do you know how I would get it to recognise, for example: "11/01/2012" in A2 and so insert "JANUARY" in B2 ?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: need formula to recognise a date and convert to text in another cell

    That's what my initial response explained. Either, use =A2 with a custom format in cell B2 or =TEXT(A2,"mmmm").

    The first option will be a date but you'll only display the month. The second will be text.


    If neither of those work, I suspect that what you have is text masquerading as a date. Perhaps the cell has been formatted as TEXT or the data has been imported from elsewhere.

    If that's the case, I believe youcan use Text to Columns to convert the text to a true date and then use one of the options outlined above.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1