+ Reply to Thread
Results 1 to 11 of 11

Does a task start or finish date fall within 3 month range of a specific date

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    7

    Does a task start or finish date fall within 3 month range of a specific date

    Hi,

    I have a project timeline in excel (example attached), column D is the task start date and column E is the task finish date and Cell H7 has a specific date entered. In column H i would like to say "yes" if the start date or end date fall with 3 months (before or after) the specified date in H7. I have thought about an IF EDATE formula but can’t work it out/not sure if it is the right path to go down. Any help would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Does a task start or finish date fall within 3 month range of a specific date

    Try

    IF(AND(D8>=EDATE($H$7,-3),D8<=EDATE($H$7,3)),"Yes","No")
    IF(AND(E8>=EDATE($H$7,-3),E8<=EDATE($H$7,3)),"Yes","No")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    10-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Does a task start or finish date fall within 3 month range of a specific date

    thanks Special-K

    first formula works, how do i get both formulas to be in the same cell, do i need to put an OR in there somewhere?

  4. #4
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Does a task start or finish date fall within 3 month range of a specific date

    Try this formula:

    =AND(D18>=EDATE($H$7;-3);D18<=EDATE($H$7;3);E18>=EDATE($H$7;-3);E18<=EDATE($H$7;3))

    You can add "Yes" and "No" via IF, if the above is True or False

  5. #5
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Does a task start or finish date fall within 3 month range of a specific date

    Full variant:

    =IF(AND(D18>=EDATE($H$7;-3);D18<=EDATE($H$7;3);E18>=EDATE($H$7;-3);E18<=EDATE($H$7;3));"Yes";"No")

  6. #6
    Registered User
    Join Date
    10-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Does a task start or finish date fall within 3 month range of a specific date

    perfect, thanks bmouse, just what i was after

  7. #7
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Does a task start or finish date fall within 3 month range of a specific date

    Glad to help, please mark the thread as SOLVED, if this takes care of your problem.

  8. #8
    Registered User
    Join Date
    10-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Does a task start or finish date fall within 3 month range of a specific date

    actually, looking a bit deeper it's not quite doing what i want.

    Start date of 1/1/15 and end date of 30/12/16 (in row 9) is displaying as false, however the start date of 1/1/15 is within 3 months of the date in H7 so would expect it to return true?
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Does a task start or finish date fall within 3 month range of a specific date

    Well, because of the formula of course. Look closely what it does. It compares the start date to H7, then the end date to H7 to determine if both dates are in range of +/- 3 months.
    AND returns TRUE if all arguments are TRUE and the end date in 2016, does not fall within +/- 3 months of 19.02.2015.

  10. #10
    Registered User
    Join Date
    10-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Does a task start or finish date fall within 3 month range of a specific date

    thanks bmouse, that makes sense. Is there a way to do either or? For example, the start date OR the end date fall within 3 months of the specified date? I've tried replacing the AND with an OR but everything returns true

    Really appreciate your help

  11. #11
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Does a task start or finish date fall within 3 month range of a specific date

    OR will return FALSE only if all arguments are false, and TRUE if any argument is true, so thats not a correct approach in this case.

    I believe that with AND you're as close as it gets for now, perhaps a different combination of formulas is possible, but I just can't see it.

+ 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. Calculate start date from finish date minus 14 working hours
    By PietBom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2014, 07:23 PM
  2. Make date ranges for graph (choose start and finish date)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2013, 09:13 AM
  3. Replies: 8
    Last Post: 03-29-2010, 09:20 PM
  4. Formula for determining if two date columns fall within specific date range
    By Igottabeme in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2006, 09:55 PM
  5. Replies: 1
    Last Post: 04-20-2006, 05:10 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