+ Reply to Thread
Results 1 to 12 of 12

Trying to high a date within a cell if it falls within a time window preceding a due date.

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Trying to high a date within a cell if it falls within a time window preceding a due date.

    Hello everyone, I'm trying to figure out the formula for getting a cell with a date to highlight a certain color if it happens to fall into a 12 month window of another date in a another cell.

    For example... In the screen below person from A through F have their own due date and their last completed assignment date.

    Excel.jpg

    What I want to do is the following:

    1. If the person completed the assignment within 12 month window preceding their due date, I want the cell to highlight green.

    2. If the person completed the assignment outside 12 month window preceding their due date - but the due date has not passed yet, I want the cell to highlight yellow

    3. If the person completed the assignment outside 12 month window preceding their due date - but the due date is passed, I want the cell to highlight red.

    Well my initial attempt was to use the conditioning format and use the between (for the first condition), less than (the second condition) and greater than function (for the third condition). Even if that was correct, I has a hard time applying it to the entire spread sheet where the everybody's date is different. So once I figure out the formula how I make it condition will only apply correctly to everyone based on their specific due date?

    this is by the the hardest excel worksheet I ever done so any any help will be appreciated.

  2. #2
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Yes, this is possible.

    You need to attached excel sheet with sample data.

    Regards,
    Suhas

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,981

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    The syntax for DATEDIF is as follows:

    =DATEDIF(Date1, Date2, Interval)

    Where:
    Date1 is the first date,
    Date2 is the second date,
    Interval is the interval type to return.

    If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.
    The Interval value should be one of
    Interval Meaning Description
    Please Login or Register  to view this content.
    Last edited by protonLeah; 08-05-2014 at 12:55 AM.
    Ben Van Johnson

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    You don't need a datedif formula, which is an unsupported function within excel

    For the yellow, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the green, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the red, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by gak67; 08-05-2014 at 01:01 AM.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  5. #5
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Quote Originally Posted by pangam_s@yahoo.com View Post
    Yes, this is possible.

    You need to attached excel sheet with sample data.

    Regards,
    Suhas
    Here is my sample date sheet. let me know what you came up with.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Quote Originally Posted by gak67 View Post
    You don't need a datedif formula, which is an unsupported function within excel

    For the yellow, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the green, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the red, as a formula within conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I tried plugging your formula into my sample spread sheet and it seem to highlight all cell red. I even changed the date to see if it fits the requirement and the color does not change accordingly. For example, in my sample spreadsheet I changed Anthony's Assignment completed date to 05/13/2015 to fit the green requirement, the cell turns red. When I changed the date to 5/13/2014 fit the yellow requirement, the cell turned red. And as for the red Is it necessary to plus in a "today ()" function to it, otherwise, how would excel know if we have already reach the due date already?
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    My apologies. I had the forulas the wrong way around. Try these instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    See attached file. I have changed your due dates so you can see it working as your sample data as loaded is all yellow.
    Assignment Tracking.xlsx

  8. #8
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Thanks. And when you try to apply this to everybody with differ due date, does the format painter work or since everybody's duy date is different I have to format them individually?

  9. #9
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Quote Originally Posted by gak67 View Post
    My apologies. I had the forulas the wrong way around. Try these instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    See attached file. I have changed your due dates so you can see it working as your sample data as loaded is all yellow.
    Attachment 337048
    Thanks you took care of the green and yellow conditions, but I still cannot get the red function to work correctly. I want anyone who has not completed their assignment to turn red. So if a person completed their assignment two years ago (outside the 12 month window between), but the due date is say 04/01/2015 I like to have it turn red indicating that the person is overdue when the calendar hits 04/1/2015 or beyond.

    So for example, a person have a 12 month window that went from 04/1/2013 to 04/1/2014, and he last completed his assignment on 02/1/2013. Now that 04/1/2014 has come and go, he is overdue and he needs to be in the red.

    But it look like you nailed the green and yellow, Thanks a lot.
    Last edited by dn7309; 08-05-2014 at 09:24 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    Sorry, I misunderstood what you meant by their due date having passed. I thought you meant they completed their assignment after their due date. Try this version: Assignment Tracking (2).xlsx

  11. #11
    Registered User
    Join Date
    08-05-2014
    Location
    Mobile, AL
    MS-Off Ver
    2010
    Posts
    12

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    That WORKED! Thanks a lot. I need to marked thread with solved now

  12. #12
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to high a date within a cell if it falls within a time window preceding a due d

    And to answer your earlier question, you can copy and paste, or use the format painter, and the conditional formatting will apply to the new cells.

+ 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. Replies: 7
    Last Post: 07-14-2017, 12:04 PM
  2. Counting if data falls on date between Start Date, End Date or Possible End Date
    By JessHasQuestions in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 07-26-2014, 08:01 AM
  3. Mark cell if location matches and date falls between date range
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2012, 07:54 PM
  4. Determining if date/time falls within spanned time
    By Bytor47 in forum Excel General
    Replies: 5
    Last Post: 09-15-2009, 01:55 PM
  5. How to shade a cell when a date falls within a certain date range
    By coedog123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2008, 11:37 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1