+ Reply to Thread
Results 1 to 5 of 5

Formula to compare dates not working

  1. #1
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2010
    Posts
    158

    Formula to compare dates not working

    Data being exported into my excel sheet is all text.
    When formatting columns P & R to Date format, I selected the 3/14/2001 option

    In cell C1 I entered the date 1/8/2019
    In cell C2 (first row of data in columns P & R) i entered IF(ISBLANK(P2),"",IF($C$1>P2,"Get Update","OK")) and copied / pasted into all rows of data in P.
    All results show OK even when they should say Get Updated
    Note - got the same result in a column I had formatted as Date and in a column formatted General.
    How do I get the dates to compare??
    Thank you very much!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    22,634

    Re: Formula to compare dates not working

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,283

    Re: Formula to compare dates not working

    I think the way that relational operators work, text is always greater than numbers (Test by entering the number 2 in a cell, then enter the text '1 into another cell and ask Excel if 2<"1" or if "1">2 - it should return TRUE.) It sounds like you have a column of "dates stored as text" and you are trying to compare them to a hand entered date serial number (1/8/2019 is stored as the number 43473). This date is a number, and numbers are always less than text strings, so the if test (date>text) is always FALSE, and the IF() function always returns the value_if_false argument.

    I expect that the fix for this will be to convert all of those "dates stored as text" into real date serial numbers. This help file has some strategies for converting numbers stored as text to numbers: https://support.office.com/en-us/art...1-c5bad0f0a885 (If you explore the "use a formula to convert" option, note that for dates, you may need to use the DATEVALUE() function instead of the VALUE() function).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2010
    Posts
    158

    Re: Formula to compare dates not working

    I just uploaded 2 files.
    1 named virgin - Totally untouched other than some info overwritten.
    Other file showing what I reported earlier.
    But, I do not see where they are attached to this thread.
    Hoping they are, but if not let me know and I will do it again.
    Thanks for your help!!!

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,283

    Re: Formula to compare dates not working

    Upon opening, I noticed that all of the dates are left aligned in the cells (the default for text data). I also note that some of them have a green error triangle indicating a "text date with 2 digit year". As final confirmation, I formatted a column as general and entered =ISTEXT(O3) and copied down and confirmed that your dates are text strings.

    Following the first suggestion in the help file I linked to, I selected column O -> Data -> Text to Columns -> Finish, and it appeared to convert all the text dates to real dates (right aligned by default). You could repeat for all columns with dates that should be dates. You should now be able to compare and sort those dates.

    Does that resolve the issue?

+ 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