+ Reply to Thread
Results 1 to 8 of 8

Excel doesn't recognize dates

  1. #1
    Registered User
    Join Date
    03-14-2018
    Location
    Tampa,FL
    MS-Off Ver
    2016
    Posts
    11

    Exclamation Excel doesn't recognize dates

    I'm trying to work with a sheet which has a column of date which has this format "Mar-30 2018 20:44". I tried using aging formula "=TODAY()-DATECELL" but it giving value error.
    Looks like column data is not recognized as date type. Please help.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    Re: Excel doesn't recognize dates

    DATECELL is not a valid function. What is your formula? Do you have a UDF in your workbook? Are you sure your dates really are dates or just text?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    Re: Excel doesn't recognize dates

    Select the entire column, go to Data - Text to Column - Next - Next - Select Date format and Destination as the first cell in the same column - Finish.
    Click the * to say thanks.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel doesn't recognize dates

    It seems to me that this "Mar-30 2018 20:44" is a text string. So, it needs to be converted to a real date.
    Try this:
    Assuming "Mar-30 2018 20:44" is in cell A1
    Enter formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 Mar-30 2018 20:44 107
    Or this shorter version
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 07-16-2018 at 09:11 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    03-14-2018
    Location
    Tampa,FL
    MS-Off Ver
    2016
    Posts
    11

    Re: Excel doesn't recognize dates

    Thanks for the response. "=TODAY()-DATECELL" is the syntax i follow. By DATACELL i mean the cell address of the DATE(Actual formula usage is =TODAY()-D2)

  6. #6
    Registered User
    Join Date
    03-14-2018
    Location
    Tampa,FL
    MS-Off Ver
    2016
    Posts
    11

    Re: Excel doesn't recognize dates

    Quote Originally Posted by PaulM100 View Post
    Select the entire column, go to Data - Text to Column - Next - Next - Select Date format and Destination as the first cell in the same column - Finish.
    Thanks.Tried this but not working.Date type is not identified even after this.

  7. #7
    Registered User
    Join Date
    03-14-2018
    Location
    Tampa,FL
    MS-Off Ver
    2016
    Posts
    11

    Re: Excel doesn't recognize dates

    Thank you..The data type shows general and i tried changing it to use the formula.Its giving value error after i use the formula.
    Last edited by AliGW; 07-16-2018 at 11:31 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,350

    Re: Excel doesn't recognize dates

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

+ 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. [SOLVED] Excel won't recognize dates - time
    By tsioumiou in forum Excel General
    Replies: 11
    Last Post: 01-30-2020, 09:15 PM
  2. VBA code in Excel doesn't recognize form from Internet Explorer
    By rodrigoo in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-26-2017, 10:11 AM
  3. [SOLVED] Excel Doesn't Recognize All Values in Line Chart
    By alcharbonneau in forum Excel General
    Replies: 4
    Last Post: 05-09-2016, 11:03 AM
  4. Excel doesn´t recognize HttpServerError 500 with webdav
    By eliziorezende in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-21-2012, 10:20 AM
  5. Excel does not recognize the info as dates
    By Dunderguden in forum Excel General
    Replies: 1
    Last Post: 08-24-2012, 05:22 AM
  6. Replies: 7
    Last Post: 11-12-2008, 04:13 PM
  7. Replies: 13
    Last Post: 03-09-2006, 08:25 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