Results 1 to 15 of 15

Thread: check if day difference less than 3 days in a range of dates

  1. #1

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    check if day difference less than 3 days in a range of dates

    dear experts,

    I need a formula to check the dates in a range which differs less than 3 days among them, I m going to highlight that cells by conditional formatting.

    Thanks in advance.

    regards,
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    I don't quite follow. Are you wanting to compare pairs of dates, like one date in column A against another in column B? or something completely different?

  3. #3

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    hi,
    thanks for your valuable time.

    I have 'n' number of dates in column A, the interval between any date should be more than 3 days else need to highlight.

    thanks in advance.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    If you're wanting to compare the date in A2 to the date in A1, for example, you'd have this in a formula-based conditional format:

    =a2-a1<4

  5. #5

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    not only between 2 dates, i need to check all the dates (between each date in the column) in the column A to be compared and should be more than 3 days.

    Thank you,
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    can you zip and attach small sample, showing which should be highlighted?

  7. #7

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    please see the image below, row 1 and 5 less than 4 days, row 3 and 5 less than 4 days, hope you get me now.

    Name:  date compare.JPG
Views: 114
Size:  26.1 KB
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    Not really...are you wanting to compare every possible pair of dates in the column?

  9. #9

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    Quote Originally Posted by vbfbryce View Post
    Not really...are you wanting to compare every possible pair of dates in the column?
    exactly ! camparing with each and every dates.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: check if day difference less than 3 days in a range of dates

    Wouldn't this be much easier if the data was sorted on the date column?
    Then you only have to compare each cell with the previous and the next cell.

  11. #11

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    Quote Originally Posted by Arnoutdv View Post
    Wouldn't this be much easier if the data was sorted on the date column?
    Then you only have to compare each cell with the previous and the next cell.
    yes I agree, it is not only that column, i have other data in other columns, i m trying to highlight when enter a date (with lesser interval) in the column.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    If you want a warning at the time they enter a date, you'd be best off using a worksheet_change event. I'll put an example together in a few.

  13. #13
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    See if this works.
    Attached Files Attached Files

  14. #14

    Thread Starter
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: check if day difference less than 3 days in a range of dates

    Quote Originally Posted by vbfbryce View Post
    See if this works.
    good work ! thanks for ur time,

    actually i m trying to do it by any formula / array formula with out any VBA code.
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  15. #15
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: check if day difference less than 3 days in a range of dates

    I don't see how you can do it without code if every single date has to be compared against every other date, unless you sort it, as Arnoutdv recommended.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width