+ Reply to Thread
Results 1 to 12 of 12

Date Comparison in row - check if dates are increasing

  1. 11-26-2015, 05:16 AM #1
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Date Comparison in row - check if dates are increasing

    Hi

    I search true internet but not find anything similar. I will explain true example.

    I want to compare dates in row if some of the dates deviates from increasing order.

    1Ex: A1: 1.1.2015 B1: 1.1.2016 C1: 1.1.2017 D1: 1.1.2018 = Correct order
    2Ex: A1: 1.1.2015 B1: 1.1.2020 C1: 1.1.2017 D1: 1.1.2018 = Wrong order
    3Ex: A1: 1.1.2015 B1: 1.1.2015 C1: 1.1.2015 D1: 1.1.2018 = Correct order
    4Ex: A1: 1.1.2015 B1: 1.1.2016 C1: 1.1.2017 D1: 1.1.2014 = Wrong order

    Any idea which combination of formulas will be OK for upper case?

    Thank you a lot!
    Reply With QuoteRegister To Reply
  2. 11-26-2015, 05:27 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,209

    Re: Date Comparison in row - check if dates are increasing

    try

    =IF(AND(A1<=B1,B1<=C1,C1<=D1),"Correct","Wrong")
    Reply With QuoteRegister To Reply
  3. 11-26-2015, 05:27 AM #3
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,060

    Re: Date Comparison in row - check if dates are increasing

    This will work, but may fail if there are blank cells in each row:

    =IF(SUMPRODUCT(--(B1:D1< A1:C1)),"Incorrect","Correct")
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 11-26-2015, 06:57 AM #4
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Date Comparison in row - check if dates are increasing

    Great, look OK and works thank you a lot! But do we have solution if blank cell show between?
    Reply With QuoteRegister To Reply
  5. 11-26-2015, 06:58 AM #5
    zeko90
    zeko90 is offline
    Registered User
    Join Date
    08-13-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Date Comparison in row - check if dates are increasing

    This work to thank you! but if you have a lot of example formula will be long!
    Reply With QuoteRegister To Reply
  6. 11-26-2015, 11:09 AM #6
    MrShorty
    MrShorty is online now
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Date Comparison in row - check if dates are increasing

    Is it necessary for the entire calculation to occur in a single cell? I rarely perform this sort of calculation, but, when I do, I spread it out over several cells (simplifies the logic inside of each cell).
    What is the source of these "blanks"? Are they true "blanks" (as in the cells are truly empty and the ISBLANK() function returns true), or are the "function blanks"/empty strings being returned by a function so that the cell is not truly "blank"? Is it possible for the first cell A1 to be blank? Is it possible at the data entry step to prevent the blanks?
    Can I assume that Excel is recognizing these dates as dates and not as text strings? My version does not automatically recognize this format as dates, so the first thing I would do is make sure that Excel sees these as dates.

    My approach would look something like this:
    1) In one helper row, check if row 1 this column is blank.
    1a) If row 1 this column is blank, return the date from the previous column
    1b) If row 1 is not blank, return the value from this column
    =if(isblank(B1),A1,B1) copied across
    2) In a second helper row, check if step 1 for this column is greater than or equal to this column.
    =B2>=A2. This should result in a row of TRUE's and FALSE's
    3) A cell at the end of row 3 that checks if all of row 3 is TRUE: =AND(A3:H3) If this cell returns TRUE, then they are correctly sorted. If not, then this cell returns FALSE.

    Will that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  7. 11-26-2015, 11:13 AM #7
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Date Comparison in row - check if dates are increasing

    I try to re-arrange the date list by sorting A-Z with SMALL(), then use MATCH :

    Please Login or Register  to view this content.
    Array formula, confirmed with Ctrl-shift-enter
    Quang PT
    Reply With QuoteRegister To Reply
  8. 11-26-2015, 11:58 AM #8
    jmhuk
    jmhuk is offline
    Registered User
    Join Date
    04-29-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    10

    Re: Date Comparison in row - check if dates are increasing

    =SUM(IFERROR(--(A2:D2>=IFERROR(SMALL(A2:D2,COLUMN(A2:D2)),MIN(A2:D2))),0))=COUNT(A2:D2)

    As an array formula I think is an extension of Glenn's idea that accounts for blanks. Returns true for correct order and false for incorrect. I haven't tested it extensively though.
    Reply With QuoteRegister To Reply
  9. 11-26-2015, 12:43 PM #9
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,060

    Re: Date Comparison in row - check if dates are increasing

    Quang.... I don't know if you saw my "cry for help" about the blanks problem in "Call in the Cavalry", or just picked up an unsolved thread; but that really does seem to nail it.

    It was making my head sore...
    Reply With QuoteRegister To Reply
  10. 11-26-2015, 06:57 PM #10
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Date Comparison in row - check if dates are increasing

    Quote Originally Posted by Glenn Kennedy View Post
    Quang.... I don't know if you saw my "cry for help" about the blanks problem in "Call in the Cavalry", or just picked up an unsolved thread; but that really does seem to nail it.

    It was making my head sore...
    Glen,
    "Cry for help" from excel guru always a big challenge ...I like it.
    Reply With QuoteRegister To Reply
  11. 11-27-2015, 01:43 PM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Date Comparison in row - check if dates are increasing

    @beebo021999,

    Good one.

    Thanks for the lesson ... again.
    Dave
    Reply With QuoteRegister To Reply
  12. 11-27-2015, 02:06 PM #12
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Date Comparison in row - check if dates are increasing

    Withdrawn by FR.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Loop check if date is between two dates
    By cvelle89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2015, 06:34 AM
  2. Simple Date Comparison Formula - When two dates are equal to each other....
    By a-train in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-10-2014, 02:12 PM
  3. Check col of dates.....each date is the first of the month
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2011, 10:06 AM
  4. Check two dates against todays date?
    By oleisbest in forum Excel General
    Replies: 12
    Last Post: 04-22-2010, 07:46 AM
  5. Excel 2007 : Increasing the value of cell and comparison
    By qsebastian in forum Excel General
    Replies: 6
    Last Post: 03-10-2010, 05:09 PM
  6. Check if date is between 2 dates
    By Shane.H in forum Excel General
    Replies: 5
    Last Post: 04-07-2009, 12:14 PM
  7. Check for increasing numbers 7 times in a row
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2008, 03:15 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1