+ Reply to Thread
Results 1 to 16 of 16

DateValue returns error

  1. #1
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    DateValue returns error

    Hi,

    I've got a number of cells that contain dates, they don't have the ' sign in front, but are left aligned and so I think they're formatted as text.

    This function seems to confirm that, saying that the Cell Data is [Text] with a [0.00] Format and contains [No Forumla].

    However, when I run DateValue on these cells I get a #Value! return. Be very grateful to learn how I can solve this problem.

    Sample data attached.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: DateValue returns error

    Hello Debatewise & Welcome to the Forum,

    If your goal is just to turn column A into "true" dates, that is, numbers...

    Convert Range of text to numbers
    Highlight the range to convert >> Data >> Data Tools >> Text to Columns >> Check Delimited >> Next >> Next >> Date >> Finish
    HTH
    Regards, Jeff

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: DateValue returns error

    The reason the DATEVALUE function doesn't work is because the entries are dates plus a time. If they were just TEXT dates then the DATEVALUE function would work.

    Another way to convert the entries to numeric dates/times...

    Select an empty cell that has never been formatted.
    Copy that empty cell: Right click>Copy
    Select the range of dates/times: Right click>Paste Special>Add>OK
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: DateValue returns error

    Thanks for the replies (and apologies for the delay in responding, I thought I'd sent this last week).

    For some reason Text to Columns isn't working on this dataset and I can't get your solution to work on it either Tony. Any ideas why?

  5. #5
    Forum Contributor satputenandkumar0's Avatar
    Join Date
    11-08-2012
    Location
    Pune, India
    MS-Off Ver
    Office xp & Office 2007
    Posts
    398

    Re: DateValue returns error

    Dear Debatewise,

    use below formula

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    Regards,
    Nandkumar S.
    ---------------------------------------------------------------
    Don't forget to Click on * if you like my solution.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    Your dates are in text form and US format
    This clumsy formula will extract the date if they are all for the current year (2013)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will handle any year
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    There must be a cleaner way but last nights' beer is still at work ...
    Attached Files Attached Files
    Last edited by Marcol; 07-20-2013 at 05:19 AM. Reason: Added Atachment
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    Or Text to columns > Fixed width > Next > Next ... Select the option Date > MDY for the first column, and skip the second if you wish.

    @ satputenandkumar did you try your suggestions in post #5 ...
    Last edited by Marcol; 07-20-2013 at 05:36 AM.

  8. #8
    Forum Contributor satputenandkumar0's Avatar
    Join Date
    11-08-2012
    Location
    Pune, India
    MS-Off Ver
    Office xp & Office 2007
    Posts
    398

    Re: DateValue returns error

    or you can try this

    Please Login or Register  to view this content.
    sorry Marcol I have not tried post #5
    Last edited by satputenandkumar0; 07-20-2013 at 05:55 AM.

  9. #9
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: DateValue returns error

    Thanks Marcol, I think I've got it working - although I seem to have done so simply by using my Mac instead of my PC. Somehow the dates are formatting correctly on here without recourse to formulas.

    I will try your suggestion when I get to my PC though and will report back.

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    Quote Originally Posted by satputenandkumar0 View Post
    or you can try this ....
    And it will not work!!!
    Why don't you try your suggestions before you post them.

  11. #11
    Forum Contributor satputenandkumar0's Avatar
    Join Date
    11-08-2012
    Location
    Pune, India
    MS-Off Ver
    Office xp & Office 2007
    Posts
    398

    Re: DateValue returns error

    Sorry Marcol for that , but post #8 is working for me.

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    @ satputenandkumar0

    Maybe so, but does it work in the sample workbook the OP posted in Post#1?

  13. #13
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: DateValue returns error

    Your solution in #6 works great Marcol, thank you.

    One question, is there a way to preserve the hours and minutes too?

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Format the cell Number format > Time (of your choice)

    [EDIT]
    Can't you use the text to columns method I suggested once I had sobered up? (Post#7).
    Last edited by Marcol; 07-21-2013 at 07:04 AM.

  15. #15
    Registered User
    Join Date
    07-13-2013
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: DateValue returns error

    I've got it working with a combination of #6 and #14. Your Text to Columns method worked too, I think the problem before is that I was using the Delimited option rather than fixed with.

    Thanks for your help with this.

  16. #16
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: DateValue returns error

    Happy to have helped ..... eventually .... sometimes beer helps, sometimes not ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to error trap a vlookup that returns an error or #N/A
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 12:01 PM
  2. [SOLVED] Datevalue - Same value, one returns error?
    By Jaymond Flurrie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2013, 11:35 AM
  3. Datevalue #Value error?
    By a94andwi in forum Excel General
    Replies: 4
    Last Post: 03-20-2007, 08:37 AM
  4. [SOLVED] IF - returns name error
    By Alan Davies in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2006, 04:25 AM
  5. [SOLVED] =DATEVALUE("7/1/20"&MID(C3,13,2)) creates error
    By Gary in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2006, 12:55 PM

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