+ Reply to Thread
Results 1 to 3 of 3

Cannot convert date (which is in text format) to date in numeric format

  1. #1
    Registered User
    Join Date
    10-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Cannot convert date (which is in text format) to date in numeric format

    Hi all,

    I have a curious problem with dates in excel. I read the other Q & A on the topic of cannot group in pivot table couple of hours ago in the forum and got some ideas and applied them all to no avail.

    Problem: The issue is that the dates are in text format. I want to convert them to dates in numeric format so that I can apply the grouping. But whatever I try it does not work. I am attaching the excel file.

    column c: the dates which are in text format
    column d: i checked if they are in text format using the function isnumber()
    column e: I get an error Value# upon applying the datevalue() function to change the date into numberic format.

    Please help. Many thanks in advance.

    Regards
    gp
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,580

    Re: Cannot convert date (which is in text format) to date in numeric format

    Put this formula in E2:

    =DATE(RIGHT(C2,2)+2000,LEFT(C2,2),MID(C2,4,2))

    and copy down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Cannot convert date (which is in text format) to date in numeric format

    Thank you so much Pete. You are awesome. God bless.Love this forum. Great experience. I post a question and get quick answer.

+ 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