+ Reply to Thread
Results 1 to 10 of 10

Set Date Range within a defined range, not to start before first date defined

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    5

    Set Date Range within a defined range, not to start before first date defined

    T&E.xlsx
    The attached document is a timeline that lists responsibilities that need to be completed within a specific date range, Meeting Turnover Date and Meeting Start Date. The dates inserted now are default dates with a 100 day planning window. What is the formual if the window of time is less than 100 days? The range must fall within the Meeting Turnover Date and Meeting Start Date

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Set Date Range within a defined range, not to start before first date defined

    Hi Chicago_girl,

    Welcome to the forum.
    I am not clear on your query..
    Do you want the dates to be calculated and appear in column F?
    would it not be just a date + / - 100 days?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    05-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Set Date Range within a defined range, not to start before first date defined

    The date in column F needs to be no later than the Meeting Start Date (for example April 6, 2012). Currently there are items that have a due date before April 6, 2012. Often meetings turn over in less than 100 days so the date in column F needs to be withig the two date range. Does that make sense? Thanks.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Set Date Range within a defined range, not to start before first date defined

    Okay..

    If you want the "Due Dates" in column F to not to exceed "Meeting Start Date) i.e, cell D2, you can use below formula in F8 and drag it down:-

    =IF($D$3+E8>$D$2,$D$2,$D$3+E8)

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    05-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Set Date Range within a defined range, not to start before first date defined

    Unfortunately, when I placed =IF($D$3+E8>$D$2,$D$2,$D$3+E8)in cell F8 to F69, the dates did not populate correctly. If I remove the value in column E, the date in column F will correct to the MEETING TURN OVER date but I would have to remove all the values in column E and that would make every responsibility have the same due date (MEETING TURN OVER DATE). I need the responsibilities that are set before the MEETING TURN OVER date (April 6, i.e., 61 days from the event) to be the MEETING TURN OVER date then any responsibilities due after the MEETING TURN OVER (55 days to event, 25 days to event, etc…) to continue with down the schedule within the appropriate timeframe. Do you have additional thoughts?
    Attached Files Attached Files

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Set Date Range within a defined range, not to start before first date defined

    Hi Chicago_girl,

    As an option, try removing the minus (-) sign from E column and check if this works...
    If you still have issues, I would like to see the dates entered manually in column F which you are expecting as an answer. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  7. #7
    Registered User
    Join Date
    05-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Set Date Range within a defined range, not to start before first date defined

    Hello,
    Removing the "-" and it does not work. I put in the correct dates in column F. If the meeting starts on June 6 and the meeting was turned over for planning on April 6, 61 days are left for planning. see attachment. You will see the some dates in Column F are before the April 6 turn over date (I marked in red). I need any dates that fall before the turnover date (April 6) to default to the turnover day and any days within the range follow column E predetermined dates. Each meeting turn over period could be different. 55days planning, 30 days planning, 100 days planning, etc...

    Thank you for your thoughts.
    Attached Files Attached Files

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Set Date Range within a defined range, not to start before first date defined

    I would like to see the dates entered manually in column F which you are expecting as an answer.
    Please remove formula from column F and enter the dates which you are expecting to be there... thanks.


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  9. #9
    Registered User
    Join Date
    05-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Set Date Range within a defined range, not to start before first date defined

    I removed the formula in column F. There were only formula's in the red area, sorry I missed that. I have now entered the dates that would correspond with column E but the dates in red are before the turn over date of April 7th. I would prefer the dates that fall before the turn over date to default to the turn over date. I understand the theory on how to accomplish that for this specific turn over date/meeting date but if I was to change either turnover or meeting date, currently, column F dates would not change.
    Attached Files Attached Files

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Set Date Range within a defined range, not to start before first date defined

    Hi Chicago_girl,

    Unfortunately, I am not getting you....
    I am looking for a statement from your side which will say F8 would be 27 Feb because when we subtract E8 (100) from D3 (June6) we get 27 Feb as answer.

    Above rule can be changed if there is some logical relation between D2,D3 and D4 and we can accommodate that.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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